formula- blank cells

[meggles]

good morning.

i've got a pricing spreadsheet that's doing my head in. hopefully someone can help me with my latest formula issue.

when i expor the file as a pdf, you loose the column names. so...

a b c d e f g h
part ID qty 1 price qty 2 price qty 3 price discount price

i want to calculate the discount price as a perctange of either b/c or d/e or f/g groupinsg (qty break & price for that paricular qty amt). The problem is b/c are populated for all rows. some parts have a secdonary price break (d/e) and some have a tiertary price break (f/g).

I want to take the percentge discount off the high qty price break. So if f/g is populated I want to take it from that. If it's not populated, then e/f. and Only if f/g & e/f are NOT populated do i want to take the discount from b/c. is there a formula i can insert into H to do this?

(i hope i've explained this well) thanks in advance.

[NBVC]

You can use a nested IF


e.g.

=IF(B2<>"",B2/C2,IF(D2<>"",D2/E2,F2/G2))

This will check if B2 is not blank, if not it will do your first formula (which you many need to change according to your formula need).

If B2 is blank, it will check D2... if not blank it will perform D2/E2... and if it is blank it will perform F2/G2

[meggles]

thanks for the quick response!

based on that info, i've inputted the following formula:

=1.33*(IF(G2>0,"G2"),IF(E2>0,"E2"),IF(C2>0,"C2"))

What am I doing wrong as this is coming up #VALUE! (?)

[NBVC]

Don't put the cell references in quotes...

also you are inserting closing parentheses in the wrong places...

try:

Please Login or Register  to view this content.

[meggles]

perfect! thank you so much! you saved me hours of cutting pasting etc!

[meggles]

hi there everyone. hopefully someone can show/tell me what i'm doing wrong here. i've attached an edited version of the spreadsheet in question.

The nested IF has gotten me this far. but now i'm stuck.

I want to extend the formula in R, to include G, E & C. however, when i attempt to extend the formula to include those rows, i get an error message.

Basically, what i need is R to give me the lowest price for a particular item. but not all items have the same number of price breaks or additional discounts. therefore... i want R to equal (in order of descending preference):
Q
P
O
N
M
L
K
J
I
H
G
E
C (last resort)

what am I doing wrong? thanks in advance.

[daddylonglegs]

You can't have more than 7 nested functions, unless you're using Excel 2007. Try this formula in Q2

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},IF(C2>0,C2,""),IF(E2>0,E2,""),LOOKUP(2,1/(G2:P2>0),G2:P2)))

If you have no >0 values in any of the cells then this will returm an error, you could make it return zero in that situation by amending to:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3,4},0,IF(C2>0,C2,""),IF(E2>0,E2,""),LOOKUP(2,1/(G2:P2>0),G2:P2)))

[meggles]

thanks for that. i have probably put a typo in the formula, is it's not returning the correct valve. can you pls double check the revised attached zip. thank you.

[daddylonglegs]

Sometimes the board adds spaces which shouldn't be there, in this case between the I and F in IF so should be

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3}, IF(C2 >0,C2,""),IF(E2>0,E2,""),LOOKUP(2,1/(G2:P2>0),G2:P2)))

[meggles]

knew it had to be something really silly! thanks!

problem solved.

if you have a sec, could you pls explain in words what this formula is doing?