There are only 6 nesting levels or 7 levels total. If you don't actually
examine the structure, it gives the impression that there are many more
levels due to number of IF's. The IF's that reference B7 in the first
argument are the parents and form a staircase structure while the remaining
IF's branch from each of these. These branches terminate before the limit is
reached.
If you prefer the lookup table approach then go ahead. But I don't think it
will be as simple as you think. SUMPRODUCT could likely do a better (simpler
and shorter) job. I thought I'd leave that to others.
Greg
" wrote:
On Apr 1, 4:07 pm, Greg Wilson
wrote:
Try this (untested):
=IF(Or(B7={"Departure", "Arrival"}),IF(Or(E7={"Sedan", "Van"}),
20,IF(E7="Limo", 22, IF(Or(E7={"Bus", "Suv"}), 30, "Undefined"))),
If(Or(B7={"Wedding", "Social"}),IF(E7="Limo", 10*N7, IF(E7="Suv", 12*N7,
"Undefined")), If(Or(B7={"Bar Run", "Night Out"}),If(E7="Sedan", 50,
If(E7="Limo", 60, IF(Or(E7={"Suv", "Bus"}), 70, "Undefined"))), If(B7 =
"Prom",IF(E7="Limo", 40, IF(Or(E7={"Suv", "Bus"}), 70, "Undefined")) ,
"Undefined"))))
Greg
Hi Greg
That is alot of IF statements, would a look up table be better. I
think you may have maxed out the number of nested IF statements you
can make (Office 2003) but unsure what version of Excel that you are
running
Dougal Everingham
Senior Solutions Consultant
CONTROL PLAN REPORT ANALYSE IMPROVE
Australia
Level 10, 390 St Kilda Road, Melbourne VIC 3004
www.satorigroup.com.au
The Satori Group is an international consulting company specialising
in designing and implementing business improvement solutions