# This formula uses more levels of nesting than you can use in the current file format.

#### Mike Habinshuti

##### New Member
I am trying to use my formula:

<colgroup><col></colgroup><tbody>
</tbody>

and it's not working getting This formula uses more levels of nesting than you can use in the current file format as message please help.

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### etaf

##### Well-known Member
i was able to use in 2016 with no problem - Removed the last Index function and replaced with a 0 , as i did not have that sheet etc
And it worked fine

Also what version of Excel you are using as excel 2016 will accept that number of nested IFs
2003 will be an issue and I think 2007 maybe also be a problem
but after that - 2010,13,16 versions should be OK
2010 has 64 levels

Although as stated not the best approach

Last edited:

#### Gerald Higgins

##### Well-known Member
Hi, welcome to the board.

That formula is a mess isn't it ?

I think I would try a completely different approach, maybe using some kind of lookup table to store all the permutations.

Something like this . . .

......................Col A........Col B.........Col C.....................Result
Row 1............CBFFH.........WDF.........CBFFH/WDF...........Non Costed
Row 2............CBFFH.........PBN..........CBFFH/PBN............Non Costed
Row 3............CBFFH.........PGAFVAI....CBFFH/PGAFVAI.....Non Costed

and so on.
Obviously I've used abbreviations because I can't be bothered typing your text out in full.

Column C can be a simple formula to concatenate the entries in Col A and Col B, such as
=A1&B1

You can then use a lookup table, something like this

=vlookup(N7&U7,C1:D10,2,false)

Where C1:D10 is the lookup table mentioned above.

This approach won't deal with ALL of your conditions, but should handle most of them in a simpler way.

#### etaf

##### Well-known Member
where you have a common output like "non costed" you can combine
=IF(AND(N7="common basket fund for HIV",OR(U7="WORLD DIABETES FOUNDATION", U7="PAYS BAS - NETHERLAND", " PROJECT: GLOBAL ALLIANCE FOR VACCINES AND IMMUNISATIONS (FRW)",U7="Global fund" ),"Non costed",

Note you have a space in front of " Project
is that in excel a space ??? remember excel needs to see the exact text OR you use wild cards *

Also then for the rest where you have
in common - you could use a Lookup or a MATCH function

IF(AND(N7="Support health care activities",U7="United states of america"),"blomberg",
IF(AND(Q7="government of rwanda",S7="own revenues"),"Own revenues","the index function"))))))))))))))))

#### thisoldman

##### Well-known Member
This is a cleaned up version of the original formula:
Code:
``````IF(AND(N7="common basket fund for HIV",  U7="WORLD DIABETES FOUNDATION"),
"Non costed",
IF(AND(N7="common basket fund for HIV", U7="PAYS BAS - NETHERLAND"),
"Non costed",
IF(AND(N7="common basket fund for HIV" ,U7=" PROJECT: GLOBAL ALLIANCE FOR VACCINES AND IMMUNISATIONS (FRW)"),
"Non costed",
IF(AND(N7="common basket fund for HIV",U7="Global fund"),
"Non costed",
IF(AND(N7="common basket fund for HIV",U7="UNIVERSITY OF THE WITWATERSRAND"),
"WOTRO",
IF(AND(N7="common basket fund for HIV",U7="UNITED Kingdom"),
"BLOOMBERG",
IF(AND(N7="common basket fund for HIV",U7="UNATED STATES AGENCY FOR INTERNATIONAL DEVELOPMENT - IDA"),
"WDF (NDPCPR)",
IF(AND(N7="common basket fund for HIV",U7="United states of america"),
"ROROS",
"WDF",
IF(AND(N7="common basket fund for HIV",U7="INTERNATIONAL DEVELOPMENT ASSOCIATION"),
"PTBI",
IF(AND(N7="common basket fund for HIV",U7="Bill gate melinda foundation"),
"UICC",
"SOMITOMO",
IF(AND(N7="common basket fund for HIV",U7="World Food Programme"),
"WFP",
"IARC",
IF(AND(N7="Support health care activities",U7="United states of america"),
"blomberg",
IF(AND(Q7="government of rwanda",S7="own revenues"),
"Own revenues",
INDEX('Sup Doc'!\$BC\$2:\$BC\$29,MATCH(N7,'Sup Doc'!\$BB\$2:\$BB\$29,0))))))))))))))))))``````

This might work as an alternative:
Code:
``````=IF(N7="common basket fund for HIV",
REPT("Non costed", OR(U7={"WORLD DIABETES FOUNDATION", "PAYS BAS - NETHERLAND",
" PROJECT: GLOBAL ALLIANCE FOR VACCINES AND IMMUNISATIONS (FRW)", "Global fund"}))
& REPT("WOTRO", U7="UNIVERSITY OF THE WITWATERSRAND")
& REPT("BLOOMBERG", U7="UNITED Kingdom")
& REPT("WDF (NDPCPR)", U7="UNATED STATES AGENCY FOR INTERNATIONAL DEVELOPMENT - IDA")
& REPT("ROROS", U7="United states of america")
& REPT("WDF", U7="Danemark")
& REPT("PTBI", U7="INTERNATIONAL DEVELOPMENT ASSOCIATION")
& REPT("UICC", U7="Bill gate melinda foundation")
& REPT("SOMITOMO", U7="JAPAN")
& REPT("WFP", U7="World Food Programme")
& REPT("IARC", U7="France"),
IF(AND(N7="Support health care activities", U7="United states of america"),
"blomberg",
IF(AND(Q7="government of rwanda",S7="own revenues"),
"Own revenues",
INDEX('Sup Doc'!\$BC\$2:\$BC\$29,MATCH(N7,'Sup Doc'!\$BB\$2:\$BB\$29,0)))))``````

I did not correct any possible misspellings that were in the original formula. All strings in quotes are just as they appeared in the original.

Last edited:

1,148,291
Messages
5,745,892
Members
423,983
Latest member
blackworx

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back