If 0 then blank

N

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
 
S

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

David Biddulph

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 said:
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 said:
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
Click to expand...
Click to expand...
 
S

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

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
 
N

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



nlp239 said:
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)))
--------------------------
Click to expand...
I have the above worksheet which I hope you will be able to understand
or cut and paste in XL.
Click to expand...
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
Click to expand...
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
Click to expand...
or if you wish, if the contents of B3 (starting km) is empty show 0
otherwise calculate the formula.
Click to expand...
Hope you can help - I've been trying for a week now.
Thanks- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Worked perfectly
 
N

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



nlp239 said:
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)))
--------------------------
Click to expand...
I have the above worksheet which I hope you will be able to understand
or cut and paste in XL.
Click to expand...
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
Click to expand...
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
Click to expand...
or if you wish, if the contents of B3 (starting km) is empty show 0
otherwise calculate the formula.
Click to expand...
Hope you can help - I've been trying for a week now.
Thanks- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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

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)
Click to expand...
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 -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...

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

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



nlp239 said:
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)))
--------------------------
Click to expand...
I have the above worksheet which I hope you will be able to understand
or cut and paste in XL.
Click to expand...
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
Click to expand...
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
Click to expand...
or if you wish, if the contents of B3 (starting km) is empty show 0
otherwise calculate the formula.
Click to expand...
Hope you can help - I've been trying for a week now.
Thanks- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Value of minutes not calculated 11
Supress DIV/0 2
Excel formula for inventory problem 3
Timesheet formula problem 3
Attendance formula for payroll please help 0
Simple Calculation Bug 1
Total Working hours If 2
Sorting Merged Sum Fields 1

Top