Is there a between 2 numbers function

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I have a cell value that changes and I want to return a
different result depnding on what criteria it meets.
For example, cell h13 changes in value:

If it is between:
1 and 5 = 1%
6 and 10 = 2%
11 and 15 = 3%
etc.

What is the best way to approach this?
 
Not sure what you want to do with that %, but..

=ROUNDUP(H13/5,0)&"%"

or

=ROUNDUP(H13/5,0)*0.01
 
Sorry Guys I think I am being misunderstood let me try
and explain it better


I have a value that changes ... lets say it is entered
into cell B3

in another cell I want a percentage based on which
criteria the number falls into. I do not have the
criteria listed anywhere.

The criteria looks like follows

0-4
5-24
25-249
250-499
500+

if the number falls between 0 and 4 I want the cell to
show 0%, if it falls under 5-24 I want the cell to
have .5% etc.

Thanks for the help ... I am not good with excel.
 
Before your specs change again, is what you entered most recently what you
really want (which looks kind of tough without a separate table, to be
honest)- or are you looking for some clues as to a solution (which were
already given)?
 
Hi
in your lookup range only use the lower boundary
A B
0 5%
5 6%
....
500 10%

Now use a formula like
=VLOOKUP(A1,'lookup_sheet'!A1:B10,2,1)
 
Yes this is what I really want I just explained it poorly
the first time ... sorry. I suppose I could create a
table outlining all the options within the criteria
except some of them are large numbers (max 15000)

I have no clue if this is possible or how to do it. I
was originally hoping I could use IF statements and some
sort of BETWEEN function but no function exists.

Thx.
 
You can get away with using an IF statement if you want. People here were
looking for a compact solution to your original problem though.

For "between", you can use AND, such as;

=IF(AND(A1>=5,A1<=15),"between 5 and 15",IF(AND(A1>=16,A1<=25),"between 16
and 25",""))

... and on and on. But it will be a bit long, and you can't go past 7 IFs
beyond the first one if you go this route.
 
I suggest:

for example: cell A1=4 then in any cell
=VLOOKUP(A1,{0;1\6;2\11;3},2)

(separators depends of your regionals settings)

Have a good time
Marek
 
Bob

How about IFs...

=IF(AND(H3>=1,H3<=5),0.01,IF(AND(H3>=6,H3<=10),0.02,IF(AND
(H3>=11,H3<=15),0.03)))

You can nest up to 7 If's... if it's beyond that, how
about making a table like:
A B
1 6 0.01
2 11 0.02
3 16 0.03
4 21 0.04
5 26 0.05
6 31 0.06
7 36 0.07
8 41 0.08

And use =VLOOKUP(H3,A1:B8,2)

Or if the ranges are a steady 5 by 5, you can use...

=CELILING(H3,5)/500

ALL RESUL CELLS SHOULD BE FORMAT AS %

Cheers
Juan
 
Dana DeLouis said:
Which way would a number like 4.5 go?
Click to expand...

That's something that often gets overlooked in such queries. I often tend to
propose something like:

=INDEX({0;0.5;1;1.5;2},MATCH(A1,{0;4;24;249;499})-(LOOKUP(A1,{0;4;24;249;499
})=A1))

with

0 0
4 0.5
24 1
249 1.5
499 2


as the table of interest.
 
Back
Top