need help with nested if function

  • Thread starter Thread starter Lori
  • Start date Start date
L

Lori

I need a function that will return a value depending on
whether the value in a cell is greater or less than 24. If
it is greater than 24, and the value of the previous cell
is equal to or greater than 24, then the value returned
should be zero; if the value is greater than 24, and the
value of the previous is less than 24, the the value
should be 20.63 times 24 minus the value of the previous
cell; if the value is less than 24 (and therefore the
value of the previous cell will also be less than 24, as
these are cumulative numbers, then the value returned
should be 20.63 x the value of the cell.
I tried it this way:
=if(c7>24ANDc6>24),0,(if(c7>24ANDc6<=24),20.63*(24-
c6),20.63*c7
This didn't work. It's probably a mess several ways.
Can anyone help me out?
TIA
Lori
 
Lori,

=IF(AND(C7>24,C6>24),0,IF(AND(C7>24,C6<=24),20.63*(24-C6),20.63*C7))


You were very close.

Regard,
Kevin
 
...
...
=IF(AND(C7>24,C6>24),0,IF(AND(C7>24,C6<=24),20.63*(24-C6),20.63*C7)) ...
Click to expand...
...

For the OP: so if the cell in question (C7 following Kevin's example) were >= 24
and the previous cell (C6) < 24, you want an implicitly incremental value
20.63*(24-C6), but if C7 < 24, you want an implicitly cumulative value 20.63*C7
?

If so, Kevin's formula could be shortened to

=20.63*IF(C7>24,MAX(0,24-C6),C7)

but this doesn't provide error checking in case C6 exceeds C7. To provide such
error checking,

=IF(C6<=C7,20.63*IF(C7>24,MAX(0,24-C6),C7),"Error: previous exceeds current")
 
Back
Top