need a nested if/and formula for a single cell sorted out - semi complex but beyond me

The spreadsheet must remain on one sheet. Cells, f3,f4,f5,and f6 are all for input purposes. The formula in cell f 8 is currently =(((F3/12)*2)*F6)+(((F4*60)+F5)*0.84)-30 and works perfectly in most cases. However, I need it to modify it's behavior under certain cases. Case 1: If f3=15 or less AND f5=22 or less, then f8 should display "FREE". Case2= Any time that f8 would return 0 or less (a negative number), f8 should display "FREE". No other cells are available for calculations, so all trickery must be preformed within cell f8.

=IF(F3<=15;IF(F5<=22,"Free"),(((F3/12)*2)*F6)+(((F4*60)+F5)*0,84)-30)

Azumi

try this
=IF(AND(F3<=15,F2<=22),"FREE",IF(((((F3/12)*2)*F6)+(((F4*60)+F5)*0.84)-30)<1,"FREE",(((F3/12)*2)*F6)+(((F4*60)+F5)*0.84)-30))

HTH =)

Originally Posted by NU2vba

try this
=IF(AND(F3<=15,F2<=22),"FREE",IF(((((F3/12)*2)*F6)+(((F4*60)+F5)*0.84)-30)<1,"FREE",(((F3/12)*2)*F6)+(((F4*60)+F5)*0.84)-30))

HTH =)

I am going to guess that the HTH =) means hope that helps or something. I tried the formula, it does not work correctly. If for example, I put in 16 in f3 and 25 in f5, in returns "FREE" in f8. That should occur only If f3=<15 AND f5=<22.

I should also have mentioned this is Excel 2010

Originally Posted by NU2vba

try this
=IF(AND(F3<=15,F2<=22),"FREE",IF(((((F3/12)*2)*F6)+(((F4*60)+F5)*0.84)-30)<1,"FREE",(((F3/12)*2)*F6)+(((F4*60)+F5)*0.84)-30))

HTH =)

Thanks azumi, and thanks NU2vba for your efforts. NU2vba, I find your formula does the same as azui's, namely that if I put in 16 in f3 and 25 in f5, in returns "FREE" in f8. That should occur only If f3=<15 AND f5=<22.

I should also have mentioned this is Excel 2010

Originally Posted by NU2vba

try this
=IF(AND(F3<=15,F2<=22),"FREE",IF(((((F3/12)*2)*F6)+(((F4*60)+F5)*0.84)-30)<1,"FREE",(((F3/12)*2)*F6)+(((F4*60)+F5)*0.84)-30))

HTH =)

Small correction to cell reference in formula- F2<=22 should read F5<=22

Well, getting closer. on this one if f3=16 and f7=24, then f8=Free. However. one of the conditions needed is that to indicate "free", f3 eeded to be 15 or less AND f5 had to be 22 or less.

Neither of those 2 conditions was met in this example, yet "Free" is still the result.

Nice try, and thank you for the effort SDCh.

Originally Posted by NU2vba

Small correction to cell reference in formula- F2<=22 should read F5<=22

Well, I changed that, but found it behaved pretty much like the solution SDCh offered, namely that:

if f3=16 and f7=23, then f8=Free. However. one of the conditions needed is that to indicate "free", f3 eeded to be 15 or less AND f5 had to be 22 or less.

Neither of those 2 conditions was met in this example, yet "Free" is still the result.

Again NU2vba, thank you for trying.