# How to return a value when a number appears in a cell, which is between two specific numbers?

#### 13tfiles

##### New Member
Essentially I have a calculator. This calculator produces values in 1 box. These values can vary and so I want to have set number ranges to categorise the results.

With this said, I want a function that takes the value from this box, evaluates what ranges it falls in, then returns a final value based on the range it falls in.

E.g. I want to have number ranges, counted in 30s so we have 301-330, 331-360 and 361-390 and so on.

Each range is assigned a number, 301-330 is 3, 331-360 is 2 and 361-390 is 1.

When a value is produced in this cell, it needs to be assigned a number, based on which range it falls in.

E.g. I get the number 385, so it falls in the range 361-390. Because it falls in this range, it is assigned the number 1.

I am at a loss on how to do this.

So far I have used this:

=COUNTIFS(AA6,">361",AA6,"<390")*1

This works, but when I try to repeat it to include other categories, it tells me I have too many arguments or asks me to add an apostrophe despite all the language being correct?

Any help be appreciated.

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### DRSteele

##### Well-known Member
Welcome to the forum.

Unless I'm missing something, I think this is straightforward. The function VLOOKUP was invented for this very purpose. I suspect you might benefit from watching a video on how this function works: https://www.youtube.com/watch?v=-hJxIMBbmZY

Keep in mind here that numbers generated by Box that are less than 301 will generate an error.

ABCD
1LowerValue
23013
33312
43611
5
6BoxValue
73851

<tbody>
</tbody>
Sheet52

Worksheet Formulas
CellFormula
D7=VLOOKUP(C7,A2:B5,2,1)

<tbody>
</tbody>

<tbody>
</tbody>

Last edited:

#### 13tfiles

##### New Member
Hmmm kind of, except is there a way to do it so you don't have the cells A1-A4 and B1 to B4? Just so its a pure formula?

#### DRSteele

##### Well-known Member
Hmmm, yes. You can hard-code the array into the formula if you must. Like this: in D7 =VLOOKUP(C7,{301,3;331,2;361,1},2,1)

#### 13tfiles

##### New Member

That is great and if I was to use the first formula you gave me, how can i include multiple boxes e.g. A2:B4, A3:B3 & A4:B2?

#### 13tfiles

##### New Member
Just found out so scratch that question. What if I wanted to include numbers that where indeed less than 301 as you mentioned?

#### 13tfiles

##### New Member
Perhaps to expand on what I was talking about, essentially I am setting time goals, now in the box, certain times (in minutes) will appear, these are marked against the set time goal. If it falls below or over the time goal, I want it to return a deviation number which is based on how far it deviates from the time goal (in 30 minute markers).

For example, the time goal is 720 minutes, if it hits 691 minutes, then I want it to return a deviation of -0.04, if it hits 749 minutes, then I want it to return a deviation of 0.04. If it hits 720 minutes precisely, I want it to return a deviation of 0.

I hope this makes sense.

Replies
4
Views
173
Replies
6
Views
86
Replies
5
Views
163
Replies
5
Views
83
Replies
3
Views
79

1,127,554
Messages
5,625,474
Members
416,109
Latest member
TripleA00123

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back