RE: Having problems to add additional info to this string....

[Greg Wilson]

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

[ad...@everingham.id.au]

On Apr 1, 4:07 pm, Greg Wilson <GregWil...@discussions.microsoft.com>
wrote:

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

dever...@satorigroup.com.au
www.satorigroup.com.au

The Satori Group is an international consulting company specialising
in designing and implementing business improvement solutions

[Carlo]

Here is my table. Not sure how to complete a table look up. I am running
Excel 2002. The sheet name is PayShedule

VAN BUS
FROM TO CAR LIMO SUV
Airport Hamilton Toronto $20 $22 $30
Airport Brantford Toronto $30 $30 $40
Bar Run Hamilton Toronto $50 $60 $70
Bar Run Hamilton N Falls $50 $60 $70

Add'l Pick Up Drop Off $2 $3 $5
Hourly Social Social $10 $10 $12
Hourly Wedding Wedding $10 $10 $12
Prom Hamilton Burlington $30 $40 $50
Prom Hamilton Other $40 $40 $70

Airport Hamilton Hamilton $12 $12 $15
Airport Grimsby Toronto $25 $25 $30
Airport Caledonia Toronto $30 $30 $40
Airport Cayuga Toronto $30 $30 $40
Airport Jarvis Toronto $30 $30 $40
Airport Hagersville Toronto $30 $30 $40
Airport Hamilton Buffalo $35 $35 $45
Airport Grimsby Buffalo $35 $35 $45
Airport Caledonia Buffalo $40 $40 $50
Airport Cayuga Buffalo $40 $40 $50
Airport Jarvis Buffalo $40 $40 $50
Airport Hagersville Buffalo $40 $40 $50
Airport Brantford Buffalo $40 $40 $50

Bar Run Hamilton Hamilton $30 $40 $50
Bar Run Hamilton Mississ $30 $50 $50
Bar Run Hamilton Oakville $30 $50 $50
Bar Run Brantford Hamilton $40 $50 $50
Bar Run Brantford Toronto $60 $70 $80
Bar Run Brantford N Falls $60 $70 $80

"Carlo" wrote:

> I have been banging my head against the table trying to ADD to this string,
> however I have had no luck. Can someone help me, if all possible please.
>
> CURRENT STRING
>
> =IF(OR(B7="Departure",B7="Arrival"),IF(OR(E7="Sedan",E7="Van"),20,IF(E7="Limo",22,IF(OR(E7="Bus",E7="Suv"),30,"Undefined"))),IF(OR(B7="Wedding",B7="Social"),IF(E7="Limo",10*N7,IF(E7="Suv",12*N7,"Undefined")),"Undefined"))
>
> Additional Info to add:
> If B7 = Bar Run or Night Out and if using a Sedan = $50, if a Limo = $60 if
> using a Suv or Bus = $70
>
> Additional Info to add:
> If B7 = Prom and if using a Limo = $40, if using Suv or Bus = $70

[Greg Wilson]

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

[Carlo]

Would you mind helping me two break this string up in 2 and 3 then.