Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Having problems to add additional info to this string....
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Having problems to add additional info to this string....
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Having problems to add additional info to this string....
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Additional info withthe POP up Calendar. | Excel Discussion (Misc queries) | |||
How do i add additional info to a floppy disk? | Excel Discussion (Misc queries) | |||
macro...additional info | Excel Discussion (Misc queries) | |||
Add additional info to multiple fields | Excel Worksheet Functions | |||
Repost with Additional Info | Excel Discussion (Misc queries) |