Need to combine 20 IF statements

Data Priority Grid test calculations.xlsHi everyone, this is my first time on the forum and I really need some help.
I have a scenario where I need to combine 20 if statements into one formula. I currently have 4 separate nested IF statements with 5 IF statements in each.

I'm trying to create one formula that defines a priority grid (i.e. whether an item is priority 1,2,3 etc) The criteria I need to evaluate is the Value, whether the status is TRUE or FALSE (this relates to a registration status) and also the number of months - calculated from Today.

I've attached the excel file which shows the criteria grid as well as the first 4 calculations I've managed to create.

Essentially I need to combine the follow 5 statements into one formula:

=IF(AND(A3>=2000000,B3="TRUE"),"1",IF(AND(A3>=2000000,B3="FALSE",DATEDIF(TODAY(),C3,"m")<6),"1",IF(AND(A3>=2000000,B3="FALSE",DATEDIF(TODAY(),C3,"m")>6,DATEDIF(TODAY(),C3,"m")<12),"2",IF(AND(A3>=2000000, B3="FALSE", DATEDIF(TODAY(), C3, "m")>12, DATEDIF(TODAY(),C3,"m")<24),"3",IF(AND(A3>=2000000, B3="FALSE", DATEDIF(TODAY(), C3, "m")>=24),"4")))))

=IF(AND(A3>=1000000, A3<2000000, B3="TRUE"),"2",IF(AND(A3>=1000000, A3<2000000,B3="FALSE", DATEDIF(TODAY(), C3, "m")<6),"2",IF(AND(A3>=1000000, A3<2000000, B3="FALSE", DATEDIF(TODAY(), C3, "m")>6, DATEDIF(TODAY(), C3,"m")<12),"3",IF(AND(A3>=1000000, A3<2000000,B3="FALSE", DATEDIF(TODAY(), C3, "m")>12, DATEDIF(TODAY(),C3,"m")<24),"4",IF(AND(A3>=1000000, A3<2000000, B3="FALSE", DATEDIF(TODAY(), C3, "m")>=24),"5")))))

=IF(AND(A3>=500000, A3<1000000, B3="TRUE"),"3",IF(AND(A3>=500000, A3<1000000,B3="FALSE", DATEDIF(TODAY(), C3, "m")<6),"3",IF(AND(A3>=500000, A3<1000000, B3="FALSE", DATEDIF(TODAY(), C3, "m")>6, DATEDIF(TODAY(), C3,"m")<12),"4",IF(AND(A3>=500000, A3<1000000,B3="FALSE", DATEDIF(TODAY(), C3, "m")>12, DATEDIF(TODAY(),C3,"m")<24),"5",IF(AND(A3>=500000, A3<1000000, B3="FALSE", DATEDIF(TODAY(), C3, "m")>=24),"6")))))

=IF(AND(A3>=200000, A3<500000, B3="TRUE"),"4",IF(AND(A3>=200000, A3<500000,B3="FALSE", DATEDIF(TODAY(), C3, "m")<6),"4",IF(AND(A3>=200000, A3<500000, B3="FALSE", DATEDIF(TODAY(), C3, "m")>6, DATEDIF(TODAY(), C3,"m")<12),"5",IF(AND(A3>=200000, A3<500000,B3="FALSE", DATEDIF(TODAY(), C3, "m")>12, DATEDIF(TODAY(),C3,"m")<24),"6",IF(AND(A3>=200000, A3<500000, B3="FALSE", DATEDIF(TODAY(), C3, "m")>=24),"7")))))

Any help would be greatly appreciated as I'm now completely stuck :-)

I'm not going to do it for you as it looks like hard work, but you have various conditions where the outcome is the same value. Therefore, you can OR these conditions instead of cascading down through an IF hierarchy. So, what I'm saying is that you have different conditions that result in a value of 1, 2, 3, etc.

As an example:

Formula:

Please Login or Register  to view this content.

could be:

Formula:

Please Login or Register  to view this content.

Only a little shorter in its own right but you can group all the conditions resulting in a value of 2 with "OR", all the conditions for 3, and so on.

Have a go, see how you get on.

Also, note that "1" is not the same as 1. The first, "1", is a text value, 1, without quotes, is numeric.

Regards, TMS

Just to highlight:

Formula:

Please Login or Register  to view this content.

You're welcome.

Simplified and combined:

Formula:

Please Login or Register  to view this content.

Seems to come out with the right answers. I don't think you can OR the conditions, as it starts with the basic premise, is A > a value, then is B True or False, then how many months.


Regards, TMS