I need a formula to work out points for a competition. they have to ride a route it as close as they can to 90 minutes. I don’t know what forumal to use when it is plus or minus. Any ideas of a formula?
50 points if they ride the route in 90 minutes.
49 points if they take either 89 or 91 minutes.
48 points if they take either 88 or 93 minutes
Etc.
How do you record the times? Is it in minutes (like 90.5) or in Excel time format (1:30:30 or 90:30) ?
Pete
I use 10:30
With your time in B1, you can use this formula:
=50-ABS((B1-"0:90:00")*24*60)
Format the cell as number with 0dp.
Hope this helps.
Pete
just tried it and it works - thanks allot :-)
before the time is put in the result is -40 - can I make it say 0 or nothing in the cell?
Perhaps,
=IF(B1="",0,50-ABS((B1-"0:90:00")*24*60))
it says there is a mistake in the formula :-(
how do you format the cell as number with 0dp. can't finding anything in the "format" with numbers like that.
Last edited by sharon9370_hals; 10-08-2012 at 10:04 AM.
The formula that Pepe gave you should work okay - are you sure you included the final close-bracket?
As regards the formatting, select that cell, right-click and choose Format cells, then on the Number tab choose Number and then set the number of decimal places to zero.
Hope this helps.
Pete
Just keep in mind that formatting does NOT change the underlying value, only what is displayed...
I copied the forumlar from this page
= IF(B1="",0,50-ABS(B1-"0:90:00")*24*60) - removed two brackets and it worked, thanks for the help
Last edited by sharon9370_hals; 10-09-2012 at 12:55 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks