"IF" Function question

[canshelteez]

Hello

I am trying to create a spreadsheet to keep track of scores. Each score has a corresponding number of points. For example if the score is between 170 and 174.5 the number of points is 1, if it is between 175 and 179.5 it is 2 and so on up to 200 where it is worth 8 points. I can't seem to figure out how to tell excel that if the score falls between two numbers display this value. I have tried it multiple ways such as =IF(D8>=170<=174.5,1, IF(D8>=175<=179.5,2, IF(D8>=180<=184.5,3, IF(D8>=185<=189.5,4, IF(D8>=190<=194.5,4, IF(D8>=195<=197.5,6, IF(D8>=198<=199.5,7, IF(D8=200,8,0)))))))). Please someone help me.... I can't figure it out! Thanks

Please e-mail me privately @ [email protected]

 

[Arvi Laanemets]

Hi

What is a pattern for your points distribution really? From your formula
follows that it'll be
170 - <175 := 1
175 - <180 := 2
180 - <185 := 3
185 - <190 := 4
190 - <195 := 4 (maybe here must be 5?)
195 - <198 := 6
198 - <199 := 7
199 - <200 := 0 (maybe here too must be 8?)
=200 := 8

When my corrections are right, then
=IF(OR(D8<170,D8>200),0,FLOOR((D8-165)*2/10,1)+AND(D8>=198,D8<=200)*(D8-197-
2*(D8=200)))


--
Arvi Laanemets
(Don't use my reply address - it's spam-trap)

Hello.

I am trying to create a spreadsheet to keep track of scores. Each score

has a corresponding number of points. For example if the score is between
170 and 174.5 the number of points is 1, if it is between 175 and 179.5 it
is 2 and so on up to 200 where it is worth 8 points. I can't seem to figure
out how to tell excel that if the score falls between two numbers display
this value. I have tried it multiple ways such as =IF(D8>=170<=174.5,1,
IF(D8>=175<=179.5,2, IF(D8>=180<=184.5,3, IF(D8>=185<=189.5,4,
IF(D8>=190<=194.5,4, IF(D8>=195<=197.5,6, IF(D8>=198<=199.5,7,
IF(D8=200,8,0)))))))). Please someone help me.... I can't figure it out!
Thanks.

 

[Trevor Shuttleworth]

=IF(AND(D8>=170,D8<=174.5),1, IF(AND(D8>=175,D8<=179.5),2,
IF(AND(D8>=180,D8<=184.5),3, IF(AND(D8>=185,D8<=189.5),4,
IF(AND(D8>=190,D8<=194.5),4,IF(AND(D8>=195,D8<=197.5),6,
IF(AND(D8>=198,D8<=199.5),7, IF(D8=200,8,0))))))))

Regards

Trevor

Hello.

I am trying to create a spreadsheet to keep track of scores. Each score

has a corresponding number of points. For example if the score is between
170 and 174.5 the number of points is 1, if it is between 175 and 179.5 it
is 2 and so on up to 200 where it is worth 8 points. I can't seem to figure
out how to tell excel that if the score falls between two numbers display
this value. I have tried it multiple ways such as =IF(D8>=170<=174.5,1,
IF(D8>=175<=179.5,2, IF(D8>=180<=184.5,3, IF(D8>=185<=189.5,4,
IF(D8>=190<=194.5,4, IF(D8>=195<=197.5,6, IF(D8>=198<=199.5,7,
IF(D8=200,8,0)))))))). Please someone help me.... I can't figure it out!
Thanks.

 

[Ron Rosenfeld]

Hello.

I am trying to create a spreadsheet to keep track of scores. Each score has a corresponding number of points. For example if the score is between 170 and 174.5 the number of points is 1, if it is between 175 and 179.5 it is 2 and so on up to 200 where it is worth 8 points. I can't seem to figure out how to tell excel that if the score falls between two numbers display this value. I have tried it multiple ways such as =IF(D8>=170<=174.5,1, IF(D8>=175<=179.5,2, IF(D8>=180<=184.5,3, IF(D8>=185<=189.5,4, IF(D8>=190<=194.5,4, IF(D8>=195<=197.5,6, IF(D8>=198<=199.5,7, IF(D8=200,8,0)))))))). Please someone help me.... I can't figure it out! Thanks.

Please e-mail me privately @ [email protected]

Your requirements are a bit ambiguous. For example, what should the result be
if the score is <170, >200, or between 174.5-175?

If that is irrelevant, then a simple VLOOKUP formula might be appropriate:

=VLOOKUP(A1,{170,1;175,2;180,3;185,4;190,5;195,6;198,7;200,8},2)

(The score vs value can also be entered and set up as a range reference).


--ron

 

[AlfD]

Hi!

Could be shorter:

=lookup(A1,{170;175;180;185;190;195;198;200},{1,2,3,4,5,6,7,8})

Al