"Between" Formula

  • Thread starter Thread starter Paul
  • Start date Start date
P

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!
 
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.
 
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
 
Back
Top