Count based on cell value between two numbers

Perhaps for the more experienced this is easy. I hope so.

I want to build a sheet that scores a point value based on data entered in
one cell. I would like to score a point for any number between 1 and 20, two
points for any number between 21 and 40, etc... I imagine it's some version
of an IF formula but I can't seem to figure it out.

Any help would be appreciated. Thanks in advance!

Hi,

Try this

=IF(A1,LOOKUP(A1,{0,21,41,61},{1,2,3,4}),"")

The if(a1 bit makes it return nothing if a1 is empty. After that 1 to 20
returns 1, 2 to 40 =2 etc.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"joemc911" wrote:

Perhaps for the more experienced this is easy. I hope so.

I want to build a sheet that scores a point value based on data entered in
one cell. I would like to score a point for any number between 1 and 20, two
points for any number between 21 and 40, etc... I imagine it's some version
of an IF formula but I can't seem to figure it out.

Any help would be appreciated. Thanks in advance!

My numbers are in column A
In B1 I have =INT(A1/20)+1; this is copied down the column
Seems to do what you want
5 1
20 2
30 2
39 2
40 3
41 3
60 4
70 4
79 4
80 5
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"joemc911" wrote in message
...
Perhaps for the more experienced this is easy. I hope so.

I want to build a sheet that scores a point value based on data entered in
one cell. I would like to score a point for any number between 1 and 20,
two
points for any number between 21 and 40, etc... I imagine it's some
version
of an IF formula but I can't seem to figure it out.

Any help would be appreciated. Thanks in advance!

Perfect! Thanks Mike! I didn't really understand the lookup function but I
was able to use your formula and teach myself a little of what the lookup did
and how to use it. Exactly what I was hoping to get from here!

"Mike H" wrote:

Hi,

Try this

=IF(A1,LOOKUP(A1,{0,21,41,61},{1,2,3,4}),"")

The if(a1 bit makes it return nothing if a1 is empty. After that 1 to 20
returns 1, 2 to 40 =2 etc.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"joemc911" wrote:

Perhaps for the more experienced this is easy. I hope so.

I want to build a sheet that scores a point value based on data entered in
one cell. I would like to score a point for any number between 1 and 20, two
points for any number between 21 and 40, etc... I imagine it's some version
of an IF formula but I can't seem to figure it out.

Any help would be appreciated. Thanks in advance!

Glad i could help and thanks for the feedback
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"joemc911" wrote:

Perfect! Thanks Mike! I didn't really understand the lookup function but I
was able to use your formula and teach myself a little of what the lookup did
and how to use it. Exactly what I was hoping to get from here!

"Mike H" wrote:

Hi,

Try this

=IF(A1,LOOKUP(A1,{0,21,41,61},{1,2,3,4}),"")

The if(a1 bit makes it return nothing if a1 is empty. After that 1 to 20
returns 1, 2 to 40 =2 etc.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"joemc911" wrote:

Perhaps for the more experienced this is easy. I hope so.

I want to build a sheet that scores a point value based on data entered in
one cell. I would like to score a point for any number between 1 and 20, two
points for any number between 21 and 40, etc... I imagine it's some version
of an IF formula but I can't seem to figure it out.

Any help would be appreciated. Thanks in advance!