Calculating scores based on age.

[acmorris]

Hello I just built a spreadsheet that will add up scores for physical fitness tests the only thing is the scores get calculated differently depending on the persons age. How do I get excel to use one formula over another based on the number in the age column?

 

[VoG]

Hello. You'll need to enlighten us as to which formulas apply to which age.

 

[acmorris]

Its just a basic math equation but I need excel to use either one or the other based on the age. Heres one =100-(M5-18)*6-(N5/10) the other one will be basically the same format I haven't taken the time to work it yet because I don't how to make excel chose automatically what formula to use.

 

[VoG]

It would be something like

=IF(A1<30,100-(M5-18)*6-(N5/10),other formula)

where A1 contains the age.

 

[acmorris]

It would be something like

=IF(A1<30,100-(M5-18)*6-(N5/10),other formula)

where A1 contains the age.

made it work thank you. Not to get demanding but how would I assign a class to each set of scores? I've tried using IF but can't quite get it. It would be if someone overal score was a
225-300=1st
200-224=2nd
185-199=3rd
184-0=fail

 

[T. Valko]

made it work thank you. Not to get demanding but how would I assign a class to each set of scores? I've tried using IF but can't quite get it. It would be if someone overal score was a
225-300=1st
200-224=2nd
185-199=3rd
184-0=fail

One way...

Assuming cell B1 = score

=IF(COUNT(B1),IF(B1>224,"1st",IF(B1>199,"2nd",IF(B1>184,"3rd","Fail"))),"")