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 |
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 |
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 |
All times are GMT +1. The time now is 04:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com