#VALUE in cell

[John]

With the follwing formula in a cell, I get the #VALUE no matter what
H39 value is
=IF(H39<=600,0),IF(AND(H39>600,H39<=630,30),IF(AND(H39>630,H39<660),SUM(H39-600),H39))

In theory, I should get either 0, 30, H39-600 or H39. No matter what,
I get #VALUE

 

[Leo Heuser]

John

Try:
=IF(H39<=600,0,IF(AND(H39>600,H39<=630),30,IF(AND(H39>630,H39<660),H39-600,H
39)))

or the shorter form:

=IF(H39<=600,0,IF(H39<=630,30,IF(H39<660,H39-600,H39)))

The shorter form works, because Excel will stop calculating
the IF-struct, when the first true condition is met.


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

 

[John]

Leo

Thanks for that. It fixed the problem. Just a quick 'please explain'
question

I can see the differences between my formula and yours, but what
difference do the differences make. If you get my meaning

 

[Leo Heuser]

John

Your problem shows an example of nested
IFs and in this situation all right-parentheses
are put at the end of the formula.

The IF construct is:

IF(Condition, do-1,do-2), which means: if Condition is
true then do-1, if it's not true i.e. False, then do-2.

Nested IFs

=IF(Condition1, do-1, IF(Condition2, do-2, IF(Condition3, do-3, do-4)))

If Condition1 is true, then do-1. If Condition1 is false, then check
if condition2 is true. If it is, do-2. If not, check if Condition3 is true.
If it is, do-3. If not, do-4. The first time Excel finds a Condition,
which is true, it returns the do-that for that condition.

SUM(H39-600)

SUM is used on a range, e.g. SUM(F4:F10) and is a shorter
form for F4+F5+F6+F7+F8+F9+F10, so instead of SUM(H39-600),
just use H39-600.

I hope my explanation shed some light on the matter

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Leo

Thanks for that. It fixed the problem. Just a quick 'please explain'
question

I can see the differences between my formula and yours, but what
difference do the differences make. If you get my meaning

39)))

or the shorter form:

=IF(H39<=600,0,IF(H39<=630,30,IF(H39<660,H39-600,H39)))

The shorter form works, because Excel will stop calculating
the IF-struct, when the first true condition is met.


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

[/QUOTE]