| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If 0 then blank
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If 0 then blank
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If 0 then blank
For the second use
=IF(B3="",0,=IF(H3<TIME(3,21,0),(D3-117)*2,IF(H3TIME(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" 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If 0 then blank
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) -- 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If 0 then blank
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If 0 then blank
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If 0 then blank
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If 0 then blank
On Oct 17, 4:48*pm, Sheeloo <="to" & CHAR(95) & "sheeloo" & CHAR(64) &
"hotmail.com" wrote: For the second use =IF(B3="",0,=IF(H3<TIME(3,21,0),(D3-117)*2,IF(H3TIME(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" 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 - Thanks, this also worked perfectly (for those who come after me) there's an extra space between the last two brackets. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If 0 then blank
On Oct 17, 5:12*pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote: 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 ... 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 - Thanks David, you're absolutely right - we live and learn. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If 0 then blank
On Oct 17, 5:21*pm, ShaneDevenshire
wrote: 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- 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Index/match - make blank cells return a blank value. | Excel Worksheet Functions | |||
| Average Formula to display blank cell if named range is blank | Excel Worksheet Functions | |||
| where can I down Blank Worksheets, blank stmt. of account forms | Excel Discussion (Misc queries) | |||
| Not showing blank and non blank items in filter mode for values | Excel Worksheet Functions | |||
| How do I make a blank cell with a date format blank? | Excel Worksheet Functions | |||
Linear Mode
