CODEWITHSUNDEEP
excelexcel-formula
robin
robin

Reputation: 181

EXCEL IF FORMULA with multiple criteria

I would like to put a remark using IF formula in column C below based on criteria:

A   B   C   
10  56% Leader
20  10% Laggard
55  45% Mover
90  90% Cashcow

Criteria:

Remark      A       B
CASHCOW :   >50     >50%
MOVER   :   >50     BELOW 50%
LEADER  :   BELOW 50    >50%
LAGGARD :   BELOW 50    BELOW 50%

I am trying to figure out with this IF formula but not working: =IF(A1&B1>=100&50%, "laggard", IF(A1&B1>=50&50%, "mover", IF(A1&B1>50&50%, "leader", "cashcow")))

Can anyone enlighten me how to get it done?

Thank you.

Upvotes: 1

Views: 245

Answers (2)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19847

This doesn't need an AND or OR just a couple of nested IFS.

If A1 (10) is greater than 50 we know it's going to be either a CASHCOW or MOVER so we just need to check if B1 (56%) is greater or less than 50%:
=IF(A1>50,IF(B1>0.5,"CASHCOW","MOVER"))

If A1 is less than 50 then the FALSE part of the function is used to check if B1 if greater or less than 50% and return LEADER or LAGGARD as required. 

=IF(A1>50,IF(B1>0.5,"CASHCOW","MOVER"),IF(B1>0.5,"LEADER","LAGGARD"))

The order of your TRUE/FALSE is important here as you don't state what happens if either value is exactly 50. The first part is saying IF A1 is greater than 50 - if it's equal to 50 then it's not greater so the FALSE part is used.

Upvotes: 1

Ace Amr
Ace Amr

Reputation: 108

This should work -

=IF(AND(A1>50,B1>50%),"CASHCOW",IF(AND(A1>50,B1<50%),"MOVER",IF(AND(A1<50,B1>50%),"LEADER",IF(AND(A1<50,B1<50%),"LAGGARD","DO NOTHING"))))

Upvotes: 2

Related Questions