PDA

View Full Version : Solved: More than 7 Nested IF's



PEV
02-21-2012, 07:40 AM
I've been trying to figure out a way to add another IF in the below formula but can't wrap my head around the impact of the variable cell, F41 in this case, then would go down one row to F42, F43, etc...

I've read up on putting it to VBA or using Named Ranges but the variable cells throw me off.

=IF(F41="","",IF(F41=$AE$27,MAX(H41,$V$13*I41),IF(F41=$AE$28,MAX(MAX(,$V$13,$B$22*E41,$D $14)*I41,H41),IF(F41="Container",H41*I41,IF(F41="flat",H41,IF(F41="CBM",MAX(MAX($V$13/E41,$B$22)*I41,H41),MAX(MAX($V$13,$B$22*E41)*I41,H41,$D$22)))))))

any ideas? the first IF F41="" is important for esthetics purposes

mdmackillop
02-21-2012, 10:27 AM
Can you post a workbook with sample data to test solutions?

PEV
02-21-2012, 11:41 AM
Here you go, hope this works

PEV
02-21-2012, 11:46 AM
sorry, new file.

Bob Phillips
02-21-2012, 12:35 PM
Try this

=IF(F30="","",IF(F30=$AE$27,MAX(H30,$V$13*I30),0)+
IF(F30=$AE$28,MAX(MAX(,$V$13,$B$22*E30,$D$14)*I30,H30),0)+
IF(F30="Container",H30*I30,0)+
IF(F30="flat",H30,0)+
IF(F30="CBM",MAX(MAX($V$13/E30,$B$22)*I30,H30),0)+
IF(OR(F30="%",F30="Skid",F30="Metric Ton"),MAX(MAX($V$13,$B$22*E30)*I30,H30,$D$22),0))

PEV
02-21-2012, 02:18 PM
Changed it a bit to match the Metric Ton way to calculate but works great. thanks...

=IF(F30="","",IF(F30=$AE$27,MAX(H30,$V$13*I30),0)+
IF(F30=$AE$28,MAX(MAX(,$V$13,$B$22*E30,$D$14)*I30,H30),0)+
IF(F30="Container",H30*I30,0)+
IF(F30="flat",H30,0)+
IF(F30="CBM",MAX(MAX($V$13/E30,$B$22)*I30,H30),0)+
IF(OR(F30="METRIC TON"),MAX($V$13/1000*I30,H30),0))

p45cal
02-21-2012, 05:03 PM
I know it's solved but..
I noticed that the formula hinges on F30, and that F30 had Data Validation based on the range AE24:AE31, so I've used that range in MATCH to return a number and used that in the CHOOSE function which will allow more choices (up to 254) if necessary in future. When you open the CHOOSE Function Arguments dialogue box, it's a lot clearer to see which formulae you're applying than doing the same with an IF function. So far in minor testing it returns the same values as yours.

=IF(F30="","",IF(ISERROR(MATCH(F30,AE24:AE28,0)),MAX(MAX($V$13,$B$22*E30)*I30,H30,$D$22) ,CHOOSE(MATCH(F30,AE24:AE28,0),MAX(MAX($V$13/E30,$B$22)*I30,H30),H30*I30,H30,MAX(H30,$V$13*I30),MAX(MAX($V$13,$B$22*E30, $D$14)*I30,H30))))

I'm using Excel 2003 at the moment and the formula can be shorter with XL2007/2010's IFERROR function, but I daren't post what I think it might be as I can't test.

PEV
02-23-2012, 12:40 PM
Thanks for the extra feedback P. havent had much time over the last few days to play around with it. I plugged your formula in my original file and it didnt seem to work. I'll test a few things and let you know if it works. I like the fact that I can add more parameters to F30

p45cal
02-23-2012, 01:54 PM
Thanks for the extra feedback P. havent had much time over the last few days to play around with it. I plugged your formula in my original file and it didnt seem to work. I'll test a few things and let you know if it works. I like the fact that I can add more parameters to F30Well, I just tested it again and it worked on your supplied file. It did it not 'seem to work' isn't very informative - in what way didn't it work?