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

[13tfiles]

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.

 

[DRSteele]

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.

	A	B	C	D
1	Lower	Value
2	301	3
3	331	2
4	361	1
5
6			Box	Value
7			385	1

<tbody>
</tbody>

Sheet52​

Worksheet Formulas Cell Formula D7 =VLOOKUP(C7,A2:B5,2,1) <tbody> </tbody>

<tbody>
</tbody>

 

[13tfiles]

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]

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]

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]

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

 

[13tfiles]

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.