Is there a between 2 numbers function

[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?

 

[Don Guillett]

Have a look in HELP index for LOOKUP, especially Vlookup

 

[Frank Kabel]

Hi
try
=(INT((A1-5)/5)+1)/100

and format as percentage

 

[Dave R.]

Not sure what you want to do with that %, but..

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

or

=ROUNDUP(H13/5,0)*0.01

 

[Bob]

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.

 

[JE McGimpsey]

Just another way:

=CEILING(H13,5)/500

Not sure what you want to do with that %, but..

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

or

=ROUNDUP(H13/5,0)*0.01

 

[Dave R.]

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)?

 

[Frank Kabel]

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)

 

[Guest]

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.

 

[Dave R.]

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.

 

[Guest]

Thanks you so much Frank ... it worked like a charm.

 

[MAK]

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

 

[Dana DeLouis]

The criteria looks like follows

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

Which way would a number like 4.5 go?

Dana

 

[Juan Sanchez]

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

 

[Aladin Akyurek]

Which way would a number like 4.5 go?

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.