Excel Column Summation

  • Thread starter Thread starter Scott Adams
  • Start date Start date
S

Scott Adams

I know this is likely an insanely simple situation, but it is early Tuesday
and I am stumped.

I have a simple spreadsheet breaking hours down by percentage to spread my
labor costs across multiple projects. All number cells are formatted the
same: Numeric with 1 decimal place. However, when I enter my total hours and
the math occurs, the resulting breakdown does not add back up to my total
hours. It is almost always off by 0.1

TOT 11.0
4% 0.4
15% 1.7
2% 0.2
31% 3.4
7% 0.8
17% 1.9
14% 1.5
5% 0.6
5% 0.6

Is there some contraint that I can place on any cell to force this to add up
correctly? I know it must be a decimal situation somewhere, but I am stumped.

THANK YOU!
Scott
 
Instead of formatting try rounding to 1 decimal place: =ROUND(C1,1)
Regards,
Stefi


„Scott Adams†ezt írta:
 
To overcome this you can use the ROUND() function on all your calculations, or
go to Tools>Options>Calculation>Precision as displayed..

If this post helps click Yes
 
Thanks Stefi and Jacob --

I updated each function to round to 1 decimal as follows:

=ROUND(H2*$C$3,1)

The sum total still is high by 0.1

11.0
4% 0.4
15% 1.7
2% 0.2
31% 3.4
7% 0.8
17% 1.9
14% 1.5
5% 0.6
5% 0.6
11.1
 
I can't follow the calculation process, please post your original data set
and your formulae!
Stefi


„Scott Adams†ezt írta:
 
Display your numbers as 2 decimal places (or as General) instead of 1, and
they will add up to 11.0.
It is because you rounded them to 1 place that you've got the wrong answer.
 
Here are the formulae. Does this help?

Hrs/Day 11.0 <= C2
%
4% =Round(C2*percentage,1) 0.4
15% =Round(C2*percentage,1) 1.7
2% =Round(C2*percentage,1) 0.2
31% =Round(C2*percentage,1) 3.4
7% =Round(C2*percentage,1) 0.8
17% =Round(C2*percentage,1) 1.9
14% =Round(C2*percentage,1) 1.5
5% =Round(C2*percentage,1) 0.6
5% =Round(C2*percentage,1) 0.6
Total: 11.1
 
As I mentioned in an earlier post, your rounding to one place is what causes
the numbers not to add up to 11.0.
Round them to 2 places, or don't bother rounding, and they'll add up to
11.0.
The values are
0.44
1.65
0.22
3.41
0.77
1.87
1.54
0.55
0.55
 
You have replied to your own message and thanked yourself. If you were
trying to reply to someone else's message, you need to select that and reply
to it.

But I guess it's still early in the morning in your part of the world? :-)
 
Back
Top