Hi,

I'm sure you already have an answer, but a few pointers on formula
construction:

1. =SUM(C3-B3) should be =C3-B3 no SUM( )
2. (D3-117)*1 should be (D3-117) no multiplying by 1
3. =IF(B3-B2=0,0,B3-B2) is the basic type of formula you are looking for.

--
Thanks,
Shane Devenshire


"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