Lessor Of or Greater of Nested Function

B

Bruce6735

Please review the following IF(AND) function [XXX] = ACTUAL RESULT
Row 20 F G H I J
[20.35] [10.00] =F21+G21 [30.35] =IF(G21<=20,G21,0) [10.00] =IF9AND(I21<20,H21>20)=TRUE,20-G21,0) [10.00]

I have a problem if the values of H is >20.00. the IF formula returns a null or "0" value in J. What I'd like for it to do in that case is return whatever the value of F happens to be. Hope this is not too confusing.

Thanks
 
D

David Biddulph

I assume that when you said
=IF9AND(I21<20,H21>20)=TRUE,20-G21,0)
you meant
=IF(AND(I21<20,H21>20)=TRUE,20-G21,0) ?
It is better to copy and paste, not to retype.

You don't need =TRUE, because you are testing a boolean variable.
=IF(AND(I21<20,H21>20),20-G21,0) would do.

I'm confused by your question, because in your example you have H > 20 and
you get 10, which is what you'd expect.
Is it the value when H is <= 20 that is worrying you? If in that case you
don't want it to return zero, but instead want it to return the value in
F21, change the formula from
=IF(AND(I21<20,H21>20),20-G21,0)
to
=IF(AND(I21<20,H21>20),20-G21,F21)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top