Solved: More than 7 Nested IF's

[PEV]

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]

Can you post a workbook with sample data to test solutions?

[PEV]

Here you go, hope this works

[PEV]

sorry, new file.

[Bob Phillips]

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]

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]

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.

[VBA]=IF(F30="","",IF(ISERROR(MATCH(F30,AE24:AE28,0)),MAX(MAX($V$13,$B$22*E30)*I 30,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))))[/VBA]

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]

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]

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

Well, 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?