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 6,087 views

## 1 answer to this question.

0 votes

Use this:

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

answered Apr 5, 2022 by
• 23,260 points

0 votes
1 answer

## Write formula to Excel with Python

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

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

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

0 votes
0 answers

## Excel formula for reverse percentage calculation

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

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

0 votes
1 answer

## Reading Excel File using Python, how do I get the values of a specific column with indicated column name?

So the key parts are to grab ...READ MORE

0 votes
1 answer

## Excel - Split cell with a delimiter and if delimiter not found get complete cell value to another cell

Select the row or column that you ...READ MORE

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

0 votes
1 answer

## Excel formula get value just greater than an other

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

0 votes
1 answer

## MS Excel - SumProduct formula with Loop

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