If 0 then blank

[nlp239]

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(H3>TIME(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(H4>TIME(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(H3>TIME(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

 

[Sheeloo]

Try in E3
=IF(D3=0,0,SUM(D3-117)*1)

 

[Sheeloo]

For the second use
=IF(B3="",0,=IF(H3<TIME(3,21,0),(D3-117)*2,IF(H3>TIME(3,21,0),
(D3-117)*1,IF(H3=TIME(3,21,0),0))) )

Pair of quotes without anything between them represent an empty cell or
empty string...
btw checking for B3=0 covers both (B3 being empty or having 0). Checking for
B3="" will retrun FALSE if B3 has 0 in it.

 

[ShaneDevenshire]

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.

 

[nlp239]

Thanks ALL for your help so far - will be trying everything out later
and WILL come back to respond to the questions and leave the feedback
- right now heading for bed after a night shift.

THANKS again, much appreciated.
jc

 

[nlp239]

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 ...

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     TotalTime      "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(H3>TIME(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(H4>TIME(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(H3>TIME(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

 

[nlp239]

For the second use
=IF(B3="",0,=IF(H3<TIME(3,21,0),(D3-117)*2,IF(H3>TIME(3,21,0),
(D3-117)*1,IF(H3=TIME(3,21,0),0))) )

Pair of quotes without anything between them represent an empty cell or
empty string...
btw checking for B3=0 covers both (B3 being empty or having 0). Checking for
B3="" will retrun FALSE if B3 has 0 in it.
--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...

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     TotalTime      "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(H3>TIME(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(H4>TIME(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(H3>TIME(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 -

Thanks, this also worked perfectly (for those who come after me)
there's an extra space between the last two brackets.

 

[nlp239]

Why SUM(D3-117)*1 ?
Why not just D3-117 ?
As far as I can see, the SUM() does nothing, and the *1 does nothing.
--
David Biddulph

"Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com"> wrote in
message

Try in E3
=IF(D3=0,0,SUM(D3-117)*1)

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(H3>TIME(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(H4>TIME(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(H3>TIME(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 -

Thanks David, you're absolutely right - we live and learn.

 

[nlp239]

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

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     TotalTime      "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(H3>TIME(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(H4>TIME(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(H3>TIME(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 -

Thanks Shane, you would be surprised how much I DON'T know.

Agreed on 1 and 2
As for 3, I'm learning - I keep wanting to insert another IF statement.