#VALUE error message

[Nick]

Hi,

I'm trying to make a nested function that will apply a
different equation to a number depending on the size of
the number. For instance, I thought I could do this by

IF(B2>1000,(1-(J9*(D18^(-1*I9)))), FALSE),IF(B2>500,(1-(J8*
(D18^(-1*I8)))),FALSE)....etc

but as soon as there are two FALSE messages, Excel returns
a #VALUE error message, which overwrites any valid answer
that may be returned.

Is there any way around this?


Many thanks for any help.

 

[J.E. McGimpsey]

IF Statements have only three arguments:

=IF(<condition>,<true branch>,<false branch>)

For nested IF's, the second IF() should go in either the true branch
or the false branch. For example:

=IF(B2>1000,1-J9*D18^(-19), IF(B2>500,1-J8*D18^(-18),FALSE))

You can substitute a third IF() statements for FALSE.

Note that you can only nest 7 layers deep. If you require more than
that, there are other techniques that will probably work better,
including using math to determine offsets, or perhaps a lookup table.

 

[Mark Graesser]

Your formula has too many argument for an IF statement. It looks like you want the second IF statement to function if the first one is False. You need to replace "FALSE" with the second IF statement. You need to keep track of your )'s because all of the IF statements will close at the end of your formula. Also keep in mind that you can only nest 7 IF statements

I rewrote you formual for the two IF statements.

=IF(B2>1000,(1-(J9*(D18^(-1*I9)))),IF(B2>500,(1-(J8*(D18^(-1*I8)))),"false")

If you need more help post you entire formula

Good Luck
Mark Graesse
(e-mail address removed)


----- Nick wrote: ----

Hi

I'm trying to make a nested function that will apply a
different equation to a number depending on the size of
the number. For instance, I thought I could do this by

IF(B2>1000,(1-(J9*(D18^(-1*I9)))), FALSE),IF(B2>500,(1-(J8
(D18^(-1*I8)))),FALSE)....et

but as soon as there are two FALSE messages, Excel returns
a #VALUE error message, which overwrites any valid answer
that may be returned

Is there any way around this


Many thanks for any help.

 

[Brad E]

Exclude the "FALSE" in your equation, but put the
next "IF" as the third argument.

=IF( If , Then , Else )
=IF(B2>1000,equation if true,equation if false)

If your "IF" is false, then to do another "IF", enter it
in the False section:
=IF(B2>1000,(1-(J9*(D18^(-1*I9)))),IF(B2>500,(1-(J8*(D18^(-
1*I8)))),IF(....
NOTE: Only 7 IF's can be nested.

 

[Nick]

many thanks, problem corrected