The formula:
=sum(h10,h14,h20,h40)
The result should be:
=sum(4,5,4,4,4,) =21
I however get 20 as a result.
Tools/Options is set to automatic and the "h" cells are formatted as numbers
(1234).
The cells are dependent on other formulas (=sum(h11:h12)/2) etc. The cells
in between is formatted to numbers (1234.10)

Any suggestions anyone

Check out whether h10,h14,h20 and h40 contain real integers..or are rounded
off. Increase the decimal points of these cells and check ..

--
Jacob


"wynand" wrote:

The formula:
=sum(h10,h14,h20,h40)
The result should be:
=sum(4,5,4,4,4,) =21
I however get 20 as a result.
Tools/Options is set to automatic and the "h" cells are formatted as numbers
(1234).
The cells are dependent on other formulas (=sum(h11:h12)/2) etc. The cells
in between is formatted to numbers (1234.10)

Any suggestions anyone

First of all check the number of cell reference you have mentioned in your
sum formula.

=sum(h10,h14,h20,h40)

1-H10
2-H14
3-H20
4-H40

In the above sum function you have referred 4 cell references.

But in your next example (i.e.) =sum(4,5,4,4,4,) =21
1-4
2-5
3-4
4-4
5-4
The numbers of values are mentioned is 5.

So correct your example and provide the clear picture of your problem.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"wynand" wrote:

The formula:
=sum(h10,h14,h20,h40)
The result should be:
=sum(4,5,4,4,4,) =21
I however get 20 as a result.
Tools/Options is set to automatic and the "h" cells are formatted as numbers
(1234).
The cells are dependent on other formulas (=sum(h11:h12)/2) etc. The cells
in between is formatted to numbers (1234.10)

Any suggestions anyone

The cells are rounded off (no decimals),
so therefore 4+5+4+4+4 should be 21 and not 20
If I use the array formula
=sum(round(h10,h14,h20,h40,1))
I get to 21 as a result
Would this formula suffice or would it affect something else I'm not aware of?

I'm surprised that you say that you use =sum(round(h10,h14,h20,h40,1)) and
get 21 as a result. With Excel 2003 that formula flags a syntax error, as I
would expect it to do. Which Excel version are you using?
--
David Biddulph

"wynand" wrote in message
...
The cells are rounded off (no decimals),
so therefore 4+5+4+4+4 should be 21 and not 20
If I use the array formula
=sum(round(h10,h14,h20,h40,1))
I get to 21 as a result
Would this formula suffice or would it affect something else I'm not aware
of?

On Fri, 18 Dec 2009 01:55:01 -0800, wynand
wrote:

The formula:
=sum(h10,h14,h20,h40)
The result should be:
=sum(4,5,4,4,4,) =21
I however get 20 as a result.
Tools/Options is set to automatic and the "h" cells are formatted as numbers
(1234).
The cells are dependent on other formulas (=sum(h11:h12)/2) etc. The cells
in between is formatted to numbers (1234.10)

Any suggestions anyone

SUM will add the *contents* of the cells. Formatting does not change the
*contents*, only what you see.

So even though you may have a cell formatted as numbers(1234) and you see 1234,
the cell might contain 1233.5 and that is the number SUM will add.

You need to use ROUND if you want the result to equal what you see. You could
also use the Precision as displayed options, but be sure you understand the
consequences.


--ron

Ron, Thank you for the advice, can you expalin the consequences of
"precision".
If I use the sum-round formula would there be any hidden consequences?


"Ron Rosenfeld" wrote:

On Fri, 18 Dec 2009 01:55:01 -0800, wynand
wrote:


SUM will add the *contents* of the cells. Formatting does not change the
*contents*, only what you see.

So even though you may have a cell formatted as numbers(1234) and you see 1234,
the cell might contain 1233.5 and that is the number SUM will add.

You need to use ROUND if you want the result to equal what you see. You could
also use the Precision as displayed options, but be sure you understand the
consequences.


--ron
.

Typing "Precision as displayed" into Excel help:

"Change when and how formulas are calculated ...
Change the precision of calculations in a workbook

Caution When you change the precision of the calculations in a workbook by
using the displayed (formatted) values, Excel permanently changes any
constant values on the worksheets in the workbook. If you later choose to
calculate with full precision, the original underlying values cannot be
restored.

1.. On the Tools menu, click Options, and then click the Calculation tab.
2.. Under Workbook options, select the Precision as displayed check box. "

--
David Biddulph

"wynand" wrote in message
...

Ron, Thank you for the advice, can you expalin the consequences of
"precision".
If I use the sum-round formula would there be any hidden consequences?


"Ron Rosenfeld" wrote:

On Fri, 18 Dec 2009 01:55:01 -0800, wynand

wrote:


SUM will add the *contents* of the cells. Formatting does not change the
*contents*, only what you see.

So even though you may have a cell formatted as numbers(1234) and you see
1234,
the cell might contain 1233.5 and that is the number SUM will add.

You need to use ROUND if you want the result to equal what you see. You
could
also use the Precision as displayed options, but be sure you understand
the
consequences.


--ron
.

On Fri, 18 Dec 2009 03:08:02 -0800, wynand
wrote:

Ron, Thank you for the advice, can you expalin the consequences of
"precision".

See Excel HELP for Precision as Displayed

If I use the sum-round formula would there be any hidden conseque

No, any consequences will be out in the open :-)
--ron