Excel formula for greater than but less than with several tiers

0 votes

I have a few hundred rows of data, and each has a number between 1 and 200, and I'd like to put them in categories of 1-5 depending on where that number is.

The categories look like this:


Zones   Min  Max
1       0    35
2       35   60
3       60   85
4       85   110
5       110  200

I want to assign it a Zone if it is greater than the Min, but less than the Max.

I have 2 formulas I've been working with to solve it. One is a nested IF AND statement:

=IF(A1<=35,1,IF(AND(A1<=60,A1>35),2,IF(AND(A1<=85,A1>60),3,IF(AND(A1<=110,A1>85),4,IF(AND(A1<=200,A1>110),2,"TOO BIG")))))

The 2nd formula attempts to use a SUMPRODUCT function:

=INDEX($C$2:$C$6,SUMPRODUCT(--(A1<=$E$2:$E$6),-- (A1>$D2:$D$6),ROW($2:$6)))

Rather than have to continue to adjust the numeric values manually, I set them as absolutes, which is why this formula is slightly different. The E column is the Max value set, and the D is the Min value set.

Any help would be appreciated!

Apr 5 in Database by Edureka
• 13,640 points
73 views

1 answer to this question.

0 votes

Use this:

=MATCH(A1,{0,35,60,85,110})

enter image description here

answered Apr 5 by gaurav
• 13,560 points

Related Questions In Database

0 votes
1 answer

Write formula to Excel with Python

Using python to add formulas to an ...READ MORE

answered Mar 31 in Database by gaurav
• 13,560 points
310 views
0 votes
1 answer

How to translate a BDS excel-formula to XBBG API for Python

The parameters are improperly specified by you. ...READ MORE

answered Apr 1 in Database by gaurav
• 13,560 points
112 views
0 votes
1 answer

How to deal with "Microsoft Excel is waiting for another application to complete an OLE action"

The first step in troubleshooting is to ...READ MORE

answered Apr 6 in Database by gaurav
• 13,560 points
101 views
0 votes
0 answers

Excel formula for reverse percentage calculation

I have a calculation which I am ...READ MORE

Apr 7 in Database by Edureka
• 13,640 points
21 views
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

Convert a number to a letter in C# for use in Microsoft Excel [duplicate]

If you are familiar with using formulas ...READ MORE

answered Feb 23 in Database by gaurav
• 13,560 points
44 views
0 votes
1 answer

how do I calculate discount with if function in excel?

Suppose a customer gets a 10 percent ...READ MORE

answered Feb 23 in Database by gaurav
• 13,560 points
634 views
0 votes
1 answer

Excel formula get value just greater than an other

We can use the Search function in ...READ MORE

answered Apr 5 in Database by gaurav
• 13,560 points
133 views
0 votes
1 answer

MS Excel - SumProduct formula with Loop

Drag this to the right of cell ...READ MORE

answered Mar 14 in Database by gaurav
• 13,560 points
28 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP