"Between" Formula

[Paul]

HELP!

I'm trying to create a formula that displays a variable
(5, 7, 8, etc) when the value cell in question lies
between two constants...?!

A simple example would be:

If D5 is between 1 and 10 then say "1", but if D5 is
between 11 and 20 then say "2", and so on...

Is there any way of doing this? I'm begining to think
not!?!

Thank you to anyone who can help me!

 

[Norman Harker]

Hi Paul!

Three possibilities here:

If you have a constant range (e.g. 1-10, 11-20, 21-30), you can derive
a formula.

With a limited number of alternatives use the AND function in your
condition for an IF function.

With more than (say) three alternatives, take a look at VLOOKUP.

Which possibility you adopt will depend upon more details as to your
data and the figures you want to show.

 

[Frank Kabel]

Hi
one way:
=IF(AND(A1>=1,A1<=10),1,"other value")

 

[Sandy Mann]

Paul,

If you mean to change the returned value for every decade in the tartget
cell ie 1 - 10 in Target cell returns 1, 11 - 20 returns 2, 21 - 30 returns
3 etc, try

=CEILING(D5,10)/10

HTH

Sandy