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