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

13tfiles

New Member
Joined
Dec 23, 2017
Messages
5
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.
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,354
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 23, 2017
Messages
5
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
Joined
Mar 31, 2015
Messages
2,354
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 23, 2017
Messages
5

ADVERTISEMENT

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
Joined
Dec 23, 2017
Messages
5
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
Joined
Dec 23, 2017
Messages
5
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,519
Messages
5,625,278
Members
416,086
Latest member
CaptainGD

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top