Excel 2007 : multiple nested if

Hi All,

I have the spreadsheet like this:

B6: dropdown list with PH/SUN
C6: Dropdown list with NIGHT
D6: Dropdown list with LATE
E6: total boxes
F6: total amount
If night (C6) and late(D6) then boxes(E6) * 7, if (c6) blank and late(D6) then boxes (e6*7)
if night (C6) then boxes(E6) * 7.5 other than that E6*7.
G6: if night and late G6 = blank or 0
G6: if day and late G6 = blank or 0
G6: if night only = blank
G6 if day then check the boxes if more than 13,then boxes-13 * 0.5

in F6 : total amount:
=IF(ISTEXT(C6),E6*7.5,IF(AND(ISTEXT(C6),ISTEXT(D6),(E6*7),),IF(OR(ISTEXT(C6)),ISTEXT(D6),ISTEXT((E6* 7)))))

it seems that the formula only refer to night and late, doesn't applicable to day and late.

Please help.

thanks a lot

If I just put that formula into cell F6 on an otherwise blank row, it returns FALSE.

I'm guessing there should be another part that is missing?

You also have an extra comma that seems to do no harm but serves no purpose:

=IF(ISTEXT(C6),E6*7.5,IF(AND(ISTEXT(C6),ISTEXT(D6),(E6*7),),IF(OR(ISTEXT(C6)),ISTEXT(D6),ISTEXT((E6* 7)))))

should be:

=IF(ISTEXT(C6),E6*7.5,IF(AND(ISTEXT(C6),ISTEXT(D6),(E6*7)),IF(OR(ISTEXT(C6)),ISTEXT(D6),ISTEXT((E6* 7)))))

Breaking down the formula:

=IF(
...(condition) ISTEXT(C6),
...(True Result) E6*7.5,
...(False Result) IF(
......(condition) AND(ISTEXT(C6),ISTEXT(D6),(E6*7)),
......(True Result) IF(
.........(condition) OR(ISTEXT(C6)),
.........(True Result) ISTEXT(D6),
.........(False Result) ISTEXT((E6* 7)))))

Somewhere along the line, you're missing a FALSE result ... but I'm not sure how to rearrange the brackets.

This: AND(ISTEXT(C6),ISTEXT(D6),(E6*7)) looks wrong as you're ANDing E6*7 which looks as though it should be a result. Maybe: AND(ISTEXT(C6),ISTEXT(D6)),E6*7, ...

This: ISTEXT((E6* 7) also looks wrong as E6*7 should be a value and, therefore, not TEXT.

However, going on your description of the formula, maybe this will work for you:

=IF(AND(C6<>"",D6<>""),E6*7,IF(AND(C6="",D6<>""),E6*7,IF(C6<>"",E6*7.5,E6*7)))


Regards

You're welcome.