Formula not working, All "(" and ")" are ok

  • Thread starter Thread starter RF
  • Start date Start date
R

RF

But when I hit the enter key, I get the error dialog box,
then in the formula bar, the last "IF" is highlighted.

This formula gets the error:
=IF(+I8<>0, INDEX(Item,+T8,K8),
IF(J8="CS",(AA8*(1+F8)),
IF(J8="CA",(Y8*(1+F8)),
IF(J8="SA",(Y8/(1-F8)),
IF(J8="SS",(AA8/(1-F8)),
IF(J8="SM",(Z8/(1-F8)),
IF(J8="PA",(Y8+F8),
IF(J8="PS",(AA8+F8),
IF(J8="PM",(Z8+F8) <----without this line it works!
)))))))))
All open parands are cloees
But this one works ok, but may not produce the desired
results without the last IF condition:

=IF(+I8<>0, INDEX(Item,+T8,K8),
IF(J8="CS",(AA8*(1+F8)),
IF(J8="CA",(Y8*(1+F8)),
IF(J8="SA",(Y8/(1-F8)),
IF(J8="SS",(AA8/(1-F8)),
IF(J8="SM",(Z8/(1-F8)),
IF(J8="PA",(Y8+F8),
IF(J8="PS",(AA8+F8),(Z8+F8)
))))))))

Any ideas?

Thanks
RF
 
RF

you have too many IFs. The maximum is seven (7). Not sure what to suggest
as an alternative approach

Regards

Trevor
 
Hi
there's a limit of 7 nested functions in Excel (which you have
exceeded). In your case it's a little bit difficult to come up with a
solution (normally you would replace multiple IF functions with VLOOKUP
and a lookup table). But try the following
1. Insert the following user defined function in one standard module:
Public_Function Eval_It(rng as range)
Eval_It=application.evaluate("=" & rng.value)
end function

2. Now on a separate sheet (e.g. call this sheet 'lookup') enter the
following values
A B
1 CS AA8*(1+F8)
2 CA Y8*(1+F8)
3 SA Y8/(1-F8)
......

3. Now use the following formula
=IF(+I8<>0,
INDEX(Item,T8,K8),IF(ISNA(MATCH(J8,'lookup'!$A$1:$A$20,0)),"not
defined",EVAL_IT(VLOOKUP(J8,'lookup'!$A$1:$B$20,2,0))))
 
Frank

much more constructive than my reply ! I ran out of steam at the "too many
IFs" recognising that a straight VLOOKUP wouldn't hack it ;-)

Regards

Trevor
 
Frank Kabel said:
. . . In your case it's a little bit difficult to come up with a
solution (normally you would replace multiple IF functions with
VLOOKUP and a lookup table). But try the following
Click to expand...
....

Two alternatives. First messy.

=IF(I8<>0,INDEX(Item,T8,K8),
IF(OR(J8={"CA","CS","PA","PM","PS","SA","SM","SS"}),
LOOKUP(J8,{"CA","CS","PA","PM","PS","SA","SM","SS"},
Y8*(1+F8)*{1,0,0,0,0,0,0,0}+
AA8*(1+F8)*{0,1,0,0,0,0,0,0}+
(Y8+F8)*{0,0,1,0,0,0,0,0}+
(Z8+F8)*{0,0,0,1,0,0,0,0}+
(AA8+F8)*{0,0,0,0,1,0,0,0}+
Y8/(1-F8)*{0,0,0,0,0,1,0,0}+
Z8/(1-F8)*{0,0,0,0,0,0,1,0}+
AA8/(1-F8)*{0,0,0,0,0,0,0,1}),"invalid"))

Second somewhat less messy.

=IF(I8<>0,INDEX(Item,T8,K8),
IF(OR(J8={"CA","CS","PA","PM","PS","SA","SM","SS"}),
CHOOSE(MATCH(J8,{"CA","CS","PA","PM","PS","SA","SM","SS"}),
Y8*(1+F8),AA8*(1+F8),Y8+F8,Z8+F8,AA8+F8,Y8/(1-F8),Z8/(1-F8),
AA8/(1-F8)),"invalid"))

However, there's still more regularity to these formula that could be
exploited, and I'd bet J8 could also equal "CM", and that value would be
Z8*(1+F8). At that point, it looks like it'd be sufficient to use the two
characters in J8 to determine the two terms needed.

When using more than 3 nested IFs, there's always a better way.
 
Harlan Grove said:
...

Second somewhat less messy.

=IF(I8<>0,INDEX(Item,T8,K8),
IF(OR(J8={"CA","CS","PA","PM","PS","SA","SM","SS"}),
CHOOSE(MATCH(J8,{"CA","CS","PA","PM","PS","SA","SM","SS"}),
Y8*(1+F8),AA8*(1+F8),Y8+F8,Z8+F8,AA8+F8,Y8/(1-F8),Z8/(1-F8),
AA8/(1-F8)),"invalid"))
Click to expand...


Hi Harlan
nice second alternative :-)

Frank
 
Hi Harlan
nice second alternative :-)
Click to expand...

No, it's still messy. Better would be

=IF(I8<>0,INDEX(Item,T8,K8),
IF(OR(J8={"CA","CS","PA","PM","PS","SA","SM","SS"}),
CHOOSE(FIND(LEFT(J8,1),"CPS"),1+F8,1,1/(1-F8))
*(INDEX(Y8:AA8,FIND(MID(J8,2,1),"AMS"))+SUMIF(J8,"P?",F8)),
"invalid"))
 
Hi Harlan
No, it's still messy. Better would be

=IF(I8<>0,INDEX(Item,T8,K8),
IF(OR(J8={"CA","CS","PA","PM","PS","SA","SM","SS"}),
CHOOSE(FIND(LEFT(J8,1),"CPS"),1+F8,1,1/(1-F8))
*(INDEX(Y8:AA8,FIND(MID(J8,2,1),"AMS"))+SUMIF(J8,"P?",F8)),
"invalid"))
Click to expand...

Hi Harlan
never satisfied with the second best solution, aren't you :-)

Just a comment: I would try not to use such formulas from a maintenace
point of view. Difficult to read after some weeks, moths, years and not
so easy to change if the conditions change. IMHO a lookup table is the
better approach if the conditions could change or someone else has to
work with this formulas also

But of course I like your formula for it's shortness (and complexity).
And is has the benefit of not requiring VBA!

Regards
Frank
 
...
...
...
Just a comment: I would try not to use such formulas from a maintenace
point of view. Difficult to read after some weeks, moths, years and not
so easy to change if the conditions change. IMHO a lookup table is the
better approach if the conditions could change or someone else has to
work with this formulas also
Click to expand...
...

Lookup tables have their place, but IMO only when *ALL* values in the lookup
table are *CONSTANTS*. Lookup tables involving formulas introduce their own
complexities and maintenance headaches. In this particular case, the apparent
correspondence of Y8, Z8 and AA8 to the second letter in J8 is much better dealt
with by

INDEX(Y8,AA8,FIND(MID(J8,2,1),"AMS"))

than by 3 separate *sets* of formulas in a lookup table. The maintenance
headache is ensuring that those formulas remain consistent.

You're not looking beyond the problem as stated. OPs seldom provide complete
specs, so inferring their intent requires guesswork and is somewhat of an art.
If the OP wanted to copy the formula in question into the next several rows,
you'd need a new entire column in your lookup table for each new cell into which
the original formula were copied. If the OP wanted to copy the formula into 256
or more rows, your lookup table approach as laid out would be fubar. Transposing
it and using HLOOKUP would address this limitation, but it'd still mean that
each new cell containing this formula would require a new row in the transposed
lookup table.

I don't know what your experience has been maintaining large workbooks, but mine
has led me to believe that large tables of what should be simple formulas are
fruitful sources of all sorts of bugs. Unless array formulas can be used to
ensure like formulas remain like, better to reduce the number of cells
containing formulas to a bare minimum. Also, there's data that supports the
hypothesis that the number of bugs in worksheets is proportional to the number
of cells containing formulas and less correlated to the complexity of the
formulas. See http://panko.cba.hawaii.edu/SSR/HOME.HTM .

As for maintanace, *NOTHING* beats documentation. The clearest and simplest
workbooks without documentation can be a bigger headache to maintain that nasty
ones with the nastier bits explained.
 
[...]
Lookup tables have their place, but IMO only when *ALL* values in the lookup
table are *CONSTANTS*. Lookup tables involving formulas introduce their own
complexities and maintenance headaches. In this particular case, the apparent
correspondence of Y8, Z8 and AA8 to the second letter in J8 is much better dealt
with by
Click to expand...

O.K. agree with you on that. Also didn't like the usage of 'pseudo'
formulas in a lookup table

[...]
You're not looking beyond the problem as stated. OPs seldom provide complete
specs, so inferring their intent requires guesswork and is somewhat
Click to expand...
of an art.

Ack. Unfortunately in most cases we don't have all requiements (but
this is also the fun in this NG to come to a solution without full
specs - at least for me). So though our approach works well (and I
doubt this is a coincidence looking at the OP's example data) it is -
as you said - an (eperienced) guess that there's this kind of
dependency.

[....]
I don't know what your experience has been maintaining large workbooks, but mine
has led me to believe that large tables of what should be simple formulas are
fruitful sources of all sorts of bugs. Unless array formulas can be used to
ensure like formulas remain like, better to reduce the number of cells
containing formulas to a bare minimum. Also, there's data that supports the
hypothesis that the number of bugs in worksheets is proportional to the number
of cells containing formulas and less correlated to the complexity of the
formulas. See http://panko.cba.hawaii.edu/SSR/HOME.HTM .
Click to expand...

Same experience on my side (e.g. large business cases with too many
conditions, dependencies and 'pseudo' accuracy). And Excel really
doesn't help finding these kind of bugs (which could have serious
business effect).
Not sure what you've encountered in your working experience but I've
seen too many financial instituions relying heavily on Excel for
pricing, budgeting and other critical applications and most of these
'applications' never have been tested!

Thanks for the link, interesting data!

[...]
As for maintanace, *NOTHING* beats documentation. The clearest and simplest
workbooks without documentation can be a bigger headache to maintain that nasty
ones with the nastier bits explained.
Click to expand...

Ack

Regards and greetings from Germany
Frank
 
Back
Top