Excel Column Summation
 

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

Excel Column Summation
 

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


€žScott Adams€� ezt Ã*rta:

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

Excel Column Summation
 

To overcome this you can use the ROUND() function on all your calculations, or
go to ToolsOptionsCalculationPrecision as displayed..

If this post helps click Yes
---------------
Jacob Skaria


"Scott Adams" wrote:

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

Excel Column Summation
 

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


"Stefi" wrote:

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


€žScott Adams€� ezt Ã*rta:

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

Excel Column Summation
 

Scott add this manually and see...It adds upto 11.1
--
If this post helps click Yes
---------------
Jacob Skaria


"Scott Adams" wrote:

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


"Stefi" wrote:

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


€žScott Adams€� ezt Ã*rta:

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

Excel Column Summation
 

I can't follow the calculation process, please post your original data set
and your formulae!
Stefi


€žScott Adams€� ezt Ã*rta:

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


"Stefi" wrote:

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


€žScott Adams€� ezt Ã*rta:

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

Excel Column Summation
 

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.
--
David Biddulph

"Scott Adams" wrote in message
...
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


"Stefi" wrote:

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


"Scott Adams" ezt írta:

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

Excel Column Summation
 

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

ISSUE CLOSED
 

Thanks! I knew it was something silly.

"Scott Adams" wrote:

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

Excel Column Summation
 

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

"Scott Adams" wrote in message
...
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

ISSUE CLOSED
 

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? :-)
--
David Biddulph

"Scott Adams" wrote in message
...
Thanks! I knew it was something silly.

"Scott Adams" wrote:

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