Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TRUNC zero giving odd results?
Hi
We use excel as a timerecording sheet whereby 7 hours and 30 minutes is entered as 7.3 In order to ensure some of the calcs are correct we use TRUNC to add the hours & minutes separately. (ie: We Sum the TRUNC part & add it to (Sum of everything - sum TRUNC part)) I have the following in a spreadsheet Cell ref D31: 7.3 D32: 7.3 D33: 7.3 D34: 7.3 D35: 0 The numbers above represent 7 hours 30 minutes for 4 days out of the 5. The total hours should be 30 (I have to add the .3 bit separately & convert to minutes then hours..) But my formula appears to be unstuck at the first hurdle: But when I run (TRUNC(SUM(D31:D35))) I get 29 and not 28. Why? Incidentally is there an easier way to add times entered as hours & minutes rather than having complicated formulaes (I am amending a spreadsheet developed by somebody else) Cheers |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TRUNC zero giving odd results?
Because you have first done the addition (7.3 * 4 = 29.2) and then truncated
the result to 29 You need to truncated and then sum with =SUM(TRUNC(D31:D35)) but this must be entered as an array formula buy pressing CTLR+SHIFT+ENTER rather than simple ENTER. Why not use a (hidden) helper column to make life simple? =TRUNC(D31) etc and add these. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email wrote in message ups.com... Hi We use excel as a timerecording sheet whereby 7 hours and 30 minutes is entered as 7.3 In order to ensure some of the calcs are correct we use TRUNC to add the hours & minutes separately. (ie: We Sum the TRUNC part & add it to (Sum of everything - sum TRUNC part)) I have the following in a spreadsheet Cell ref D31: 7.3 D32: 7.3 D33: 7.3 D34: 7.3 D35: 0 The numbers above represent 7 hours 30 minutes for 4 days out of the 5. The total hours should be 30 (I have to add the .3 bit separately & convert to minutes then hours..) But my formula appears to be unstuck at the first hurdle: But when I run (TRUNC(SUM(D31:D35))) I get 29 and not 28. Why? Incidentally is there an easier way to add times entered as hours & minutes rather than having complicated formulaes (I am amending a spreadsheet developed by somebody else) Cheers |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TRUNC zero giving odd results?
To answer second part: enter the time values as 7:30
best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email wrote in message ups.com... Hi We use excel as a timerecording sheet whereby 7 hours and 30 minutes is entered as 7.3 In order to ensure some of the calcs are correct we use TRUNC to add the hours & minutes separately. (ie: We Sum the TRUNC part & add it to (Sum of everything - sum TRUNC part)) I have the following in a spreadsheet Cell ref D31: 7.3 D32: 7.3 D33: 7.3 D34: 7.3 D35: 0 The numbers above represent 7 hours 30 minutes for 4 days out of the 5. The total hours should be 30 (I have to add the .3 bit separately & convert to minutes then hours..) But my formula appears to be unstuck at the first hurdle: But when I run (TRUNC(SUM(D31:D35))) I get 29 and not 28. Why? Incidentally is there an easier way to add times entered as hours & minutes rather than having complicated formulaes (I am amending a spreadsheet developed by somebody else) Cheers |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TRUNC zero giving odd results?
That is correct if you want to truncate the total, but if you want to
truncate each, use =SUM(TRUNC(D31:D35)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message ups.com... Hi We use excel as a timerecording sheet whereby 7 hours and 30 minutes is entered as 7.3 In order to ensure some of the calcs are correct we use TRUNC to add the hours & minutes separately. (ie: We Sum the TRUNC part & add it to (Sum of everything - sum TRUNC part)) I have the following in a spreadsheet Cell ref D31: 7.3 D32: 7.3 D33: 7.3 D34: 7.3 D35: 0 The numbers above represent 7 hours 30 minutes for 4 days out of the 5. The total hours should be 30 (I have to add the .3 bit separately & convert to minutes then hours..) But my formula appears to be unstuck at the first hurdle: But when I run (TRUNC(SUM(D31:D35))) I get 29 and not 28. Why? Incidentally is there an easier way to add times entered as hours & minutes rather than having complicated formulaes (I am amending a spreadsheet developed by somebody else) Cheers |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TRUNC zero giving odd results?
You could add it all in one step
=INT(SUM(D1:D4))+MOD(SUM(MOD(D1:D4,1)/60*100),1)/100*60 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message ups.com... Hi We use excel as a timerecording sheet whereby 7 hours and 30 minutes is entered as 7.3 In order to ensure some of the calcs are correct we use TRUNC to add the hours & minutes separately. (ie: We Sum the TRUNC part & add it to (Sum of everything - sum TRUNC part)) I have the following in a spreadsheet Cell ref D31: 7.3 D32: 7.3 D33: 7.3 D34: 7.3 D35: 0 The numbers above represent 7 hours 30 minutes for 4 days out of the 5. The total hours should be 30 (I have to add the .3 bit separately & convert to minutes then hours..) But my formula appears to be unstuck at the first hurdle: But when I run (TRUNC(SUM(D31:D35))) I get 29 and not 28. Why? Incidentally is there an easier way to add times entered as hours & minutes rather than having complicated formulaes (I am amending a spreadsheet developed by somebody else) Cheers |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TRUNC zero giving odd results?
A-ha! Thanks ever so much. I don't understand about ARRAY formulas or
anything, but at least I know what to do to fix it! I'd certainly never have found that out.. In a previous version of the timesheet, the times were entered as 07:30 but for some reason the author changed it in this version. I'll perhaps contact him by email & ask why (he has now left our company!) Thanks again. Bernard Liengme wrote: Because you have first done the addition (7.3 * 4 = 29.2) and then truncated the result to 29 You need to truncated and then sum with =SUM(TRUNC(D31:D35)) but this must be entered as an array formula buy pressing CTLR+SHIFT+ENTER rather than simple ENTER. Why not use a (hidden) helper column to make life simple? =TRUNC(D31) etc and add these. best wishes -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup on pivot table results = #N/A | Excel Worksheet Functions | |||
Very large workbook now giving incorrect results :( | Excel Discussion (Misc queries) | |||
Very large complex workbook is now giving me the wrong results :( | Excel Worksheet Functions | |||
abdualmohsn | Excel Discussion (Misc queries) | |||
Equation giving unexpected results | Charts and Charting in Excel |