Nesting

[JoeExcelHelp]

Hi, I seem to be drawing a blank maybe thinking this too much but can someone assist with simplifying the 2 formulas for me.. I exceeded the nesting limit (7) which seems odd i thought that was changed to over 80 in 2010?.. thanks in advance

1st Formula

Code: Select all

IF(AND(G$14="F",G$28<0),$M$52-G$18,IF(G$14="F",$M$52,IF(G$14=$Q$51,$Q52,IF(G$14=$P$51,$P52,IF(G$14=$O$51,$O52,IF(G$14=$N$51,$N52,IF(OR(G$14=$M$51,G$14="B1P"),$M52,"-")))))))

2nd Formula

Code: Select all

IF(AND(G$14="F",G$28<0),$M$53-G$19,IF(AND(G$14="F",G$28>0),$M$53+G$16,IF(G$14=$Q$51,$Q53,IF(G$14=$P$51,$P53,IF(G$14=$O$51,$O53,IF(G$14=$N$51,$N53,IF(OR(G$14=$M$51,G$14="B1P"),$M53,"-")))))))

[HansV]

In an Excel 97-2003 workbook (*.xls) the old limit still holds. The new limit is for Excel 2007 and later workbooks (*.xlsx, *.xlsm and *.xlsb).

Do these work for you?

=IF(G$14="F",$M$52-IF(G$28<0,G$18,0),IFERROR(HLOOKUP(G$14,$M$51:$Q$52,2,FALSE),IF(G$14="B1P",$M52,"-")))

=IF(G$14="F",$M$53+IF(G$28<0,-G$19,G$16),IFERROR(HLOOKUP(G$14,$M$51:$Q$53,3,FALSE),IF(G$14="B1P",$M53,"-")))

[JoeExcelHelp]

Perfect thank you

[JoeExcelHelp]

The strange thing is Im using xlsm and it still restricts me at 7?.. is thr a setting i should look for?

[HansV]

Does the title bar of your workbook contain [Compatibility Mode]?

[JoeExcelHelp]

Yes

[HansV]

I think you'll have to set the default file type to one of the new formats.
Select File > Options.
Select Save in the navigation pane on the left hand side.
The "Save files in this format" dropdown is probably still set to "Excel 97-2003 workbook (*.xls)".
Change it to "Excel workbook (*.xlsx)" (or to .xlsm if you prefer).
Click OK.
Quit and restart Excel.

[JoeExcelHelp]

Thanks