The help file says (I believe) that IFs can be nested 64 deep.
That is: =IF(G5>0,average(G5:G9),IF(G6>0,average(G6:G10),.. ... etc.

I currently have such a formula/function in my spreadsheet which contains 7 IFs and it works OK. But if I try to add an additional "IF", I get an error.

Any ideas? I can send/email an example sheet if need be.

Thanks for any help,



Jim

Hi Jim,

Excel 2003 supports a maximum of 8 nested IFs.

Perhaps you could tell us exactly what you're trying to achieve.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

I have a list of golf rounds data, with handicap differencials in column G. I need, based on the number of rounds in the list, to average a different number of differencials based on how many rounds have been played. I as going to use nested if's - for example:
=IF(G05>0,AVERAGE(G05:G14),IF(G06>0,AVERAGE(G06:G1 4), etc...
This would require about 20 nested IFs.
Guess I misinterpreted what I read about 64 IFs allowed.

Currently, the data is residing in Row 05 to row 24, with the available data ending at row 24 (variable nr of blank lines beginning in row 05). Based on the nr of available lines of data, I need to average a variable nr of cells in column G.

I think the last ELSE argument can point to a continuation cell (to chain together the nested IFs) but not sure how to have the result always end up in the right cell without absolute referencing.


Here is the HELP text that I was referring to. It made me think more than 8 IFs could be nested...

Up to 64 IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. (See Example 3 for a sample of nested IF functions.)

Any advise appreciated, thanks in advance.

Jim S

Hi Jim,

Your profile says you're using Excel 2003, which is what my response applied to. The help you're referring to comes from a later version. And, even if you're working with Excel 2007 or later, if the workbook is in the older 97-2003 format, that still won't support more than 8 nested IFs. In any event, there are better ways than using a plethora of nested IFs (which can become very difficult to debug & modify).

If you can post a copy of the workbook with representative data, I or someone else here might be able to suggest a better approach/solution.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

The attached file contains dummy data and an example of the sheet I'd be working with. It also notes exactly what calculations are required.

Thanks again for your assistance.

Jim S

Attached Files

IF Example.xls (20.5 KB, 16 views)

Hi Jim,

Try the attached.

As you'll see, I've inserted 3 columns with round & offset values that the handicap formula in I1 uses to work out which rows to draw the averages from. You can hide those columns if you don't want to see them.

Do note that, if you change the # of rows before the handicap table starts, the offsets will need to be adjusted accordingly.

Attached Files

IF Example.xls (22.5 KB, 27 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Macropod:

It'll take me several days to disect and try to understand your formula.
I certainly would not have come up with your solution.

Thanks very much for your help.

Jim S

Many thanks Macropod, all is well.
Case closed.

Jim S