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

Mike Habinshuti

New Member
Joined
Mar 1, 2018
Messages
1
I am trying to use my formula:
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",IF(AND(N7="common basket fund for HIV",U7="Danemark"),"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",IF(AND(N7="common basket fund for HIV",U7="JAPAN"),"SOMITOMO",IF(AND(N7="common basket fund for HIV",U7="World Food Programme"),"WFP",IF(AND(N7="common basket fund for HIV",U7="France"),"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))))))))))))))))))

<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
Joined
Oct 24, 2012
Messages
5,115
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Mar 26, 2007
Messages
9,116
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
Joined
Oct 24, 2012
Messages
5,115
Office Version
  1. 365
Platform
  1. MacOS
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
N7="common basket fund for HIV"
in common - you could use a Lookup or a MATCH function

then your left with
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
Joined
Jan 5, 2014
Messages
1,074
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",
               IF(AND(N7="common basket fund for HIV",U7="Danemark"),
                 "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",
                     IF(AND(N7="common basket fund for HIV",U7="JAPAN"),
                       "SOMITOMO",
                       IF(AND(N7="common basket fund for HIV",U7="World Food Programme"),
                         "WFP",
                         IF(AND(N7="common basket fund for HIV",U7="France"),
                           "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:

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top