Significant digit issue

[musicgold]

Hi,

I am using an Excel sheet to add decimal numbers with more than five significant digits. In the file, I also use User Defined Functions to add numbers based on their formatting.

I have realized that the answers I get using the regular SUM function and using the UDF do not match after initial 3 or 4 significant digits. I am not sure which one is more accurate. And as I am adding 30-40 numbers together, even small variances are resulting in a big difference.

Is there a way I can force Excel to use all significant digits in its calculations?

Thanks,

MG.

[joms]

Hope this is what you want..have tried experimenting using the round function? to round off the decimal places..

for example:

Round ( expression, [ decimal_places ] )

Round(210.55,1) will retrurn 210.6
Round(210.559,2) will return 210.56

[musicgold]

Thanks joms.

No, that is not what I am struggling with. Maybe I should have given an example.

Consider the following. I am adding 5 numbers.

Excel’s SUM function gives me : 503.159310000

While my UDF gives me: 503.159301758

Note that the two answers differ after the first four significant digits.

[will1128]

What are the 5 numbers you are adding together? Is there any mulitiplicaton in those 5 numbers?

[musicgold]

What are the 5 numbers you are adding together? Is there any mulitiplicaton in those 5 numbers?

No.

BTW I cross posted this question here http://www.mrexcel.com/forum/showthread.php?p=2583207#post2583207

[Aussiebear]

You waited 30 minutes then cross posted into another forum..... Don't you have any faith in the members of this forum?

[Paul_Hossler]

I am using an Excel sheet to add decimal numbers with more than five significant digits. In the file, I also use User Defined Functions to add numbers based on their formatting..

I'd tend to believe that Excel is more accurate than a UDF.

Without seeing the UDF or the data, I'd guess that since you're adding "based on their formatting" you're losing precision by not using the actual full double from the cell

Attaching a sample would make it easier

Paul

[Artik]

I recommend reading this (http://support.microsoft.com/kb/78113/en)article (http://support.microsoft.com/kb/78113/en).

Artik