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.....

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

Does that help?

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


"Betty P." wrote:

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.....

=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].

"Betty P." wrote:

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.....

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" wrote:

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

Does that help?

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


"Betty P." wrote:

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.....

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


"Ron Coderre" wrote:

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" wrote:

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

Does that help?

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


"Betty P." wrote:

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.....

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.

"Betty P." wrote:

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.....

"Philip J Smith" wrote:
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.




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.

"Betty P." wrote:

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.....

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

"Ron Coderre" wrote:

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

Does that help?

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


"Betty P." wrote:

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.....