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

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

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

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

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

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