How do I enter formula for < or > numbers.

[Betty P.]

I am trying to enter a formula in a worksheet. I want the formula to do the
following: IF A1=40 then enter 1, if less that 40 enter 0, OR IF A1=45 enter
2, if less that 45 but more than 40 enter 1, OR IF A1=50 enter 3, if less
than 50 but more than 45 enter 2......and so on until the final one reads if
65 or greater enter 6.
Please help.....

 

[Ron Coderre]

Try this:
=IF((A1>=40),FLOOR((A1-40)/5,1)+1,0)

Does that help?

***********
Regards,
Ron

 

[bpeltzer]

=MAX(MIN(6,INT((A1-35)/5)),0). Most of the work is done by int((a1-35)/5).
The rest contains the result to the range [0,6].

 

[Ron Coderre]

Actually, these are shorter methods:
=MAX(INT(($A$1-35)/5),0)
or
=FLOOR(MAX(($A$1-35)/5,0),1)


***********
Regards,
Ron

 

[Ron Coderre]

I just noticed the max criteria of 6 (I slow down when it gets late...sheesh):

Consequently, these would be appropriate:
=MIN(6,FLOOR(MAX(($A$1-35)/5,0),1))
or
=MIN(6,MAX(INT(($A$1-35)/5),0))

***********
Regards,
Ron

 

[Philip J Smith]

These guys are brilliant on formulae, but you might find the following easier
to understand.

On a new worksheet enter the following in cells a1:e7

0 0
40 1
45 2
50 3
55 4
60 5
65 6

Name this range as "Scores"

In any cell - say D4 enter a score.

In cell d5 type =vlookup(d4,Scores,2)

Anything below 40 will get a 0, anything greater than, 0r equal to 40 but
less than 45 will get a 1 and so on.

If the values you want to test are scores that need grading, then the
numbers 0 to 6 can be replaced by F to A respectively and the formula will
still work.

 

[joeu2004]

These guys are brilliant on formulae, but you
might find the following easier to understand.
[....]
In cell d5 type =vlookup(d4,Scores,2)

Geesh, talk about killing an ant with a sledgehammer!
"Everything should be as simple as possible, but no
simpler." -- Einstein.

 

[Betty P.]

Thank you for all the help it was greatly appreciated, I will try the
formulas and see which works best for me.