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, 2022 in Database by Edureka
• 13,670 points
6,163 views

1 answer to this question.

0 votes

Use this:

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

enter image description here

answered Apr 5, 2022 by gaurav
• 23,260 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, 2022 in Database by gaurav
• 23,260 points
3,402 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, 2022 in Database by gaurav
• 23,260 points
1,859 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, 2022 in Database by gaurav
• 23,260 points
1,131 views
0 votes
0 answers

Excel formula for reverse percentage calculation

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

Apr 7, 2022 in Database by Edureka
• 13,670 points
716 views
0 votes
0 answers

How do I query for all dates greater than a certain date in SQL Server?

I tried this: SELECT * FROM dbo.March2010 A WHERE ...READ MORE

Aug 29, 2022 in Database by Kithuzzz
• 38,010 points
572 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, 2022 in Database by gaurav
• 23,260 points
571 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, 2022 in Database by gaurav
• 23,260 points
1,605 views
0 votes
1 answer

MS Excel - SumProduct formula with Loop

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

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