Two-condition If formula

Originally Posted by jwhitwell

How does that work if he's using drop-boxes? Values in B3 and C3 won't actually be "Max" and "Sell", respectively; they'll be a numeric value... Won't they? Maybe I missed something.

I assumed (and looks like I was right based on the OP's response) that he meant he had Data Validation drop down... not Forms/Controls drop downs...

yes sorry its data validation drop down, but now i have another problem

=IF(AND(B3="EURCHF",C3="Sell"),D3-J17,IF(AND(B3="EURCHF",C3="Buy"),D3+J17,(IF(AND(B3="EURGBP",C3="Sell"),D3-J13,IF(AND(B3="EURGBP",C3="Buy"),D3+J13,IF(AND(B3="EURJPY",C3="Sell"),D3-J19,IF(AND(B3="EURJPY",C3="Buy"),D3+J19, IF(AND(B3="USDCHF",C3="Sell"),D3-J15,IF(AND(B3="USDCHF",C3="Buy"),D3+J17,0)))))))))

Its complaining that the last AND(...) formula has something wrong with it, but as far as i can see its fine! Any ideas?

You have to many nested functions... Excel 2003 and earlier only allow up to 7 nested functions...

You can try creating 7 separate IFs and add the results... they should all return 0 except for the one that has the right combo...so when added together you should get the correct result...

Try:

=IF(AND(B3="EURCHF",C3="Sell"),D3-J17,0)+IF(AND(B3="EURCHF",C3="Buy"),D3+J17,0)+IF(AND(B3="EURGBP",C3="Sell"),D3-J13,0)+IF(AND(B3="EURGBP",C3="Buy"),D3+J13,0)+IF(AND(B3="EURJPY",C3="Sell"),D3-J19,0)+IF(AND(B3="EURJPY",C3="Buy"),D3+J19,0)+IF(AND(B3="USDCHF",C3="Sell"),D3-J15,0)+IF(AND(B3="USDCHF",C3="Buy"),D3+J17,0)