Calculating points based on time

[gruntingmonkey]

I'm trying to get a formula that calculates points up to a maximum of 50 depending on how many minutes a result is. IE

Cell A2 has an actual time in it
Cell B2 has a guess time in it
Cell C2 has a calculation which shows as points (spot on, 50 points, 1 minute under or over, 49 points, 2 minutes, under or over 48 points.. down to 0)


I have come up with the following which works for times under an hour but does not work if its over an hour
=IF(SUM(50-SUM(1440*(A2-B2)))<0,0,SUM(50-SUM(1440*(A2-B2))))

I'm guessing I can use the min/max function when I have the right formula!

Can anyone help?

 

[AlanY]

try

=MAX(0,50-(ABS(B2-A2)*24*60))

 

[wrightyrx7]

EDIT: Scrap my idea, AlanY has a better one lol

 

[gruntingmonkey]

This is great - Thanks for that. Just wondering if you know a calculation for weight thats similar.IE

Actual and guess weight - last cell has a calculation which shows as points (spot on, 50 points, .1kg under or over, 40 points, .2kg, under or over 30 points.. down to 0)



Orrrrr, even better if you can do it in pounds and ounzes.

 

[AlanY]

This is great - Thanks for that. Just wondering if you know a calculation for weight thats similar.IE

Actual and guess weight - last cell has a calculation which shows as points (spot on, 50 points, .1kg under or over, 40 points, .2kg, under or over 30 points.. down to 0)

similar

=MAX(0, 50-ABS(A2-B2))