Using multiple AND, OR conditions in formula

[G.Manikandan]

Dear Masters,

Greetings. I am stuck in a situation where, I have to use multiple AND and multiple OR condition combined with IF. I have attached the file with details. If any one can please help me?

My sincere thanks for you.

Regards,
gm2612

 

[NARAYANK991]

Hi ,

I am not able to understand your requirements.

Will there be only one classification result , or will there be one result each for the F3 column , F2 column and F1 column ?

What is the significance of the values in cells D3 , E3 and F3 ?

What is the significance of the values in cells D9 , E9 and F9 , as well as the values in cells G5 through G9 ?

Can you manually put in the value which will be the result if your conditions are applied to the data ?

Narayan

 

[G.Manikandan]

Dear Narayan,

Thanks for reply. Yes, there will be only one result, consolidating all F1,F2 and F3. Classification A / B / C will be applicable only if all the three criteria are met for the respective.

Classification will be A, if the value in cell D5 is = 34, AND, value in cell E6 is less than 6, AND, if the total score percentage Cell G10 value is greater than 90.

Similarly for Classification B, if the value in cell D5 is = 34, AND, value in cell E6 is less than 15, Greater than 10, AND, if the total score percentage Cell G10 value is greater than 75.

Classification will be C, if the value in cell D5 is less than 34, OR, value in cell E6 is Greater than 15, OR, if the total score percentage Cell G10 value is less than 75.

Values in cell D3, E3 and F3 are number of criteria used in a separate checklist, in each F3, F2 and F1 category. But, these values are just for reference, and not taken in the calculations.

Can you please support to crack the problem?

Thank you so much for your valuable time for helping me

Regards,
Mani

 

[NARAYANK991]

Hi Mani ,

Sorry , but it is still not clear why you are referring only to D5 , E6 and G10 ; what about the values in the other cells in the range D5:F8 ?

Let me paraphrase the conditions for the various classifications :

C : (D5 < 34) OR (E6 > 15) OR (G10 < 75)

B : (D5 = 34) AND (E6 > 10) AND (E6 < 15) AND (G10 > 75)

A : (D5 = 34) AND (E6 < 6) AND (G10 > 90)

Is this correct ?

You realize that there may be values for D5 , E6 and G10 for which the classification formula does not have any outputs ; in case these cells get these values , the output of the formula will be False , unless you wish to assign some other value.

Narayan

 

[G.Manikandan]

Dear Narayan,

because of travel, could not check the mails. I am sorry.

Actually, the values in the cells D5:F8 are a part of the report, and they are just informatory in the table

decision criteria are the values in cells D5, E6, and G10 only.

A slight correction I would like to indicate in the paraphrase you have beautifully putforth

C : (D5 < 34) OR (E6 > 15) OR (G10 < 75)

B : (D5 = 34) AND (E6 > 10) AND (E6 < 15) AND (G10 > 75) AND (G10<90)
A : (D5 = 34) AND (E6 < 6) AND (G10 > 90)

I am sorry, I am unable to understand the explanation you have given in the last paragraph. Can you please help me?

Thanks and regards,
Mani

 

[NARAYANK991]

Hi Mani ,

We have 3 variables , in cells D5 , E6 and G10.

D5 can take two states , D5 < 34 and D5 = 34.

E6 can take three states , E6 < 6 , E6 between 10 and 15 , E6 > 15

G10 can similarly take 3 states , G10 < 75 , G10 between 75 and 90 , G10 > 90

These 3 variables with these different states can have a total of 2 x 3 x 3 = 18 different combinations.

Out of these 18 combinations , you have considered only 3 combinations for which you are specifying outputs ; for the remaining 15 combinations , in the absence of a specified output , Excel will return the value False.

Of course , if the data is such that these 15 combinations will never occur in practice , there is no problem.

Try the following :

=IF(OR(D5 < 34,E6 > 15,G10 < 75), "C", IF(AND(D5 = 34,E6 > 10,E6 < 15,G10 > 75,G10<90), "B", IF(AND(D5 = 34,E6 < 6,G10 > 90), "A", "Not Specified")))

Narayan

 

[G.Manikandan]

Dear Narayan,

Great, this work. But, with curiosity, what will be solution if other combinations also occur in this case? I need to check the possibility for it. Can you please guide?

The reason is that, even if other combinations occur, the result should end up in C.

Many thanks for the timely help and precious effort to help me

With regards,
Mani

 

[NARAYANK991]

Hi Mani ,

Since we are using many conditions in one check , as you add more and more combinations , the only solution will be to use more IF statements.

If you can envisage all the possibilities at this stage , we can see whether alternatives are available.

Narayan