CODEWITHSUNDEEP
excelexcel-formula
Geo1288
Geo1288

Reputation: 3

A function to automate the classification of value ranges

How could I classify this data according to these criteria?

A B
1 Af Class
2 73.73
3 68.10
4 39.69
5 50.51
6 18.23
Class Range
Class 1 Af < 21 or 68 <= Af
Class 2 21 <= Af < 42 or 55 <= Af < 68
Class 3 42 <= Af < 55

I've tried using IF OR but without success.

Upvotes: 0

Views: 115

Answers (1)

Jack Deeth
Jack Deeth

Reputation: 3357

The criteria can be simplified:

if 42 <= Af < 55:
    Class 3
else if 21 <= Af < 68:
    Class 2
else:
    Class 1

This can be expressed in a formula, formatted to match the layout of the pseudocode:

=IF(AND(42 <= @A:A, @A:A < 55),
    "Class 3",
 IF(AND(21 <= @A:A, @A:A < 68),
    "Class 2",
    "Class 1"
))

Or in a single line:

=IF(AND(42 <= @A:A, @A:A < 55),"Class 3", IF(AND(21 <= @A:A, @A:A < 68), "Class 2", "Class 1"))

Upvotes: 2

Related Questions