Forum Discussion
Solved
Formula or if statement for multiple conditions
Hello All
I have a quick question.
I want to categorize my data in three categories like Good Average and Bad, the whole sheet consists of different groups. Below is the example of data, the file is attached as well.
| S/N | Groups | Amount | Category | Scale for Group A | ||
| 1 | A | 10 | 12 to 15 | Good | ||
| 2 | A | 12 | 16 to 20 | Average | ||
| 3 | A | 2 | Else | Bad | ||
| 4 | A | 30 | ||||
| 5 | A | 12 | ||||
| 6 | A | 12 | Scale for Group B | |||
| 7 | A | 3 | 50 to 60 | Good | ||
| 8 | A | 9 | 61 to 70 | Average | ||
| 9 | B | 58 | Else | Bad | ||
| 10 | B | 69 | ||||
| 11 | B | 35 | ||||
| 12 | B | 55 | Scale for Group C | |||
| 13 | C | 2 | 1 to 2 | Good | ||
| 14 | C | 3 | 3 to 4 | Average | ||
| 15 | D | 1 | Else | Bad | ||
| 16 | D | 1 | ||||
| 17 | D | 2 | ||||
| 18 | D | 6 | Scale for Group D | |||
| 19 | D | 9 | 4 to 6 | Good | ||
| 20 | D | 0 | 7 to 10 | Average | ||
| 21 | D | 3 | Else | Bad | ||
| 22 | D | 3 |
Thanks in advance
Hello Muhammad
Restrcuture your scale tables into one table.
Group - Lower - Category
A - 0 - Bad
A - 12 - Good
A - 16 - Average
A - 21 - Bad
etc.
In my example the new table is in columns I:K.
Then this formula in D2:
=LOOKUP(2,1/($I$2:$I$17=B2)/($J$2:$J$17<=C2),$K$2:$K$17)
4 Replies
Hello Muhammad
Restrcuture your scale tables into one table.
Group - Lower - Category
A - 0 - Bad
A - 12 - Good
A - 16 - Average
A - 21 - Bad
etc.
In my example the new table is in columns I:K.
Then this formula in D2:
=LOOKUP(2,1/($I$2:$I$17=B2)/($J$2:$J$17<=C2),$K$2:$K$17)
- Hello Detlef Lewin
Thank you very much for your response, my understanding of Excel is very basic level. It will be highly appreciated if you send me the file in which you did the calculation.
Regards
Muhammad KhanHere it is.
