FORMULA TO MULTIPLY

[Undertaker]

hello

i just got into this forum and it looks like it a good one, i hope you can help me.

i created an invoice in excel, the columns that i have are the followings:

item number, quantity, description, parts price, labor price, and total amount.

i would like to get a formula, that multiplies the quantity, times the parts price, only IF the parts price cell has a value, BUT if the cell of labor price has a value, then the quantity times labor price, those two multiplications in the column of total amount.

i don't even know if that is possible. can you help me?

thx.

[acknak]

You could use something as simple as =Qty*Part_price + Qty*Labor_price since whichever price is blank will be interpreted as zero in the calculation. The formula will allow a price for both parts and labor--keeping them separate is up to you when you enter the data.

If you really want it to be one or the other and never both, you can use something like =Qty*IF(ISBLANK(Part_price); Labor_price; Part_price)

[Undertaker]

hello acknak:

thank you for your answer, it was fast and good, . but i have another question, let me apologize for being such annoying.

i have the formula done, but the result of the formula shows me the "#value!" error, i do not have any value in the invoice everything is completely blank, but i put the item number, it shows me everything, because i have the "vlookup" formula.

what do i have to do to get rid of that error, to make the result, zero or blank?

thx.

Undertaker.

[acknak]

I can't tell what the problem is without more information. You should not get an error just because there are no values in the sheet.

You will need to copy/paste some of the formulas from your sheet, or even better, just attach your empty sheet to this topic, or use one of the free file-sharing sites (filecrunch.com, mediafire.com, e.g.). Or, if you prefer, you can email it to me (see "Interests" in my profile).

[Undertaker]

hello

is your e-mail _____?

because it is explained jes at martnet dot com, i am just verifying.

but before sending you the file, i just want you to know that i have everything in "vlookup formulas, that means only typing the item number it shows the descripcion, either if is a part or labor.

well, in fact is the IF(ISNA(VLOOKUP formula.

but anyways i am sending you the file.

[acknak]

Correct--but it is not good practice to display a verbatim email address in forum posts, as people can and will collect the addresses and abuse them.

Mine's already in every spam list on the planet, so it's not such a big deal, but for future reference: don't do that. That's also why people write the address in that funny way, so it is less likely to be "harvested" by spammers.

[acknak]

Ok, I looked at the file. It looks like you are almost there.

The primary problem seems to be with the cell formats.

The reason that your VLOOKUPs are failing is that the search criterion (column A, the item number on the invoice) is using a "Number/General" format, but your item numbers (column B on Sheet 2) are text values. So item "0001" becomes "1" and there is no item "1", so the VLOOKUP fails. I do not see the #VALUE error, however, so I don't know where that comes from.

The only other suggestion I have is:

Try a format like this for your amounts (column K): [$$-409]#,##0.00;[RED]-[$$-409]#,##0.00;""

Note there are three parts there, separated by semicolons. It breaks down like this:
Format if value is > 0 ; Format if value is < 0 ; Format if value is zero

That way, the blank lines have an amount of zero and will then display "", i.e. nothing.

If you have some items that have zero cost, and you want that to show on the invoice, then you'll have to build a formula to do it, but otherwise the format is easier.

As for the "Terms", you're on the right road there as well. Just expand the "Terms" table to have a second column that gives the number of days, then for the "Due Date", just use Invoice_date+VLOOKUP(Terms_entry;Terms;2;0). I.e. just get the actual number of days by looking up the specified terms in the table.

[Undertaker]

hello,

let's see, so what would you recommend, to change the format of the column item number? because as you can see, i have the format in text/

the #value! error it is in the total amount column (K).

now
this are formats cells, right? : [$$-409]#,##0.00;[RED]-[$$-409]#,##0.00;""

is this a formula :
Format if value is > 0 ; Format if value is < 0 ; Format if value is zero

and that is what i want that if the item number has no value, nor the quantity, then the descripction, parts price and labor must not have a value, neither the total amount cell, only when i enter the item number value.

the blank lines have an amount of zero therefore it will display a "", value.

sorry the inconvenience.

try it on the file and if you can do it please help.

thx.

[acknak]

change the format of the column item number? because as you can see, i have the format in text/

I would say make all the item number cells have Text format. The item numbers are really not numeric values. What would it mean to add two item numbers? Nonsense: they aren't numeric values, they're only tags that uniquely identify an item.

the #value! error it is in the total amount column (K).

Unfortunately, I don't see that error in the file you sent me, so I can't say what might be causing it.

this are formats cells, right? ... is this a formula :

Yes, that's a cell format. The second (colored) part is not a formula, it's just an explanation of how the format works.