On Oct 17, 4:43*pm, Sheeloo <="to" & CHAR(95) & "sheeloo" & CHAR(64) &
"hotmail.com" wrote:
Try in E3
=IF(D3=0,0,SUM(D3-117)*1)
--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...



"nlp239" wrote:
Team Name *"Starting
km" * "Finishing
km" * Total km * * * *"Penalty Points
Official Distance 117km
1 penalty point for
Each km over/under" * Start Time * * *Finish Time * * Total Time * * *"Penalty Points
Official Length 175 minutes
(2h 55m)
1 point = late
2 points = early"
Dancer * * * * * * * * * * =SUM(C3-B3) * * =SUM(D3-117)*1 * * * * * * * * *=SUM(G3-F3) * * =IF(H3<TIME(3,21,0),
(D3-117)*2,IF(H3TIME(3,21,0),(D3-117)*1,IF(H3=TIME(3,21,0),0)))
Prancer * * * * * * * * * *=SUM(C4-B4) * * =SUM(D4-117)*1 * * * * * * * * *=SUM(G4-F4) * * =IF(H4<TIME(3,21,0),
(D4-117)*2,IF(H4TIME(3,21,0),(D4-117)*1,IF(H4=TIME(3,21,0),0)))
--------------------------

I have the above worksheet which I hope you will be able to understand
or cut and paste in XL.

In E3 [(=SUM(D3-117)*1] I get -117 because I can't work-out how to
show 0 if the contents of D3 [=SUM(C3-B3)] is 0

The same goes for another cell containing the following:
=IF(H3<TIME(3,21,0),(D3-117)*2,IF(H3TIME(3,21,0),
(D3-117)*1,IF(H3=TIME(3,21,0),0)))
I get -234

or if you wish, if the contents of B3 (starting km) is empty show 0
otherwise calculate the formula.

Hope you can help - I've been trying for a week now.
Thanks- Hide quoted text -

- Show quoted text -

Worked perfectly