Pivot table multiplication calculation doesn't work 1

[DomP]

I am adding percentage values. These cells are NOT part of a pivot table but their values are calculated from pivot table cells.

Option 1: =SUM(B22:F22)/5
Option 2: =(B21+C21+D21+E21+F21)/5

I then take the result from above and multiply this by a fixed value in another cell.

The results that I receive are not accurate. Any ideas or more info needed?

Thanks,
Dom

 

[SkipVought]

Hi,

The results that I receive are not accurate.

Exactly what do you mean by that?

Please post a concrete example of 1) all relevant values, 2) the current results and 3) the expected results.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DomP]

Hi Skip,

For example.

The percent value = 27.23% or .2723 * 28,000,000

The result should by 7,624,400

I get 7,624,820.06

$420.06 difference

Dom

 

[SkipVought]

The DIFFERENCE (420.06) as a percentage of the expected result (7,624,400) is
[tt]
0.00005509417134457660000
[/tt]
When you are working with floating point arithmetic, that difference is insignificant.

However, when you are dealing with money, its not.

SOMEWHERE, I'd GUESS its your percentage, is not EXACTLY .2723!!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DomP]

Are you familiar with the link below? I ask because the percentages that are being provided are coming from formulas that look like this: =IF(C26=0,"0%",C26/C20)

The c20 is the total from the pivot table and the c26 is a sum from select fields in the pivot table.

http://support.microsoft.com/kb/211470

 

[SkipVought]

I don't care WHERE the values are coming from. The fact is that the quotient likey has far more precision than 4 decimal places and is causing the imprecision in your result.

Have you checked the ACTUAL VALUE of the quotient?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

It does not matter WHERE the values are coming from. The fact is that the quotient likely has far more places than 4 decimal places you posted, and is causing the imprecision in your result.

Have you checked the ACTUAL VALUE of the quotient?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Please post the actual values of C26 and C20.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DomP]

C20 = 103,342

C26 = 9,000

 

[SkipVought]

That quotient is
[tt]
0.087089469915426400000000000000
[/tt]
It's THIS PART that is causing your problem. Can you see that?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DomP]

Yes, I'm following where you are going but the quotient is stored in a cell defined with 0 decimals so the value is actually 9% or .09. These five values are then averaged producing a similar percentage with no decimals.

Are you stating that what I'm showing as 9% and what is actually be used for the calculation are different? If so, how can I combat that?

 

[SkipVought]

but the quotient is stored in a cell defined with 0 decimals ...

Absolutely incorrect!

Formatting a value changes ABSOLUTELY NOTHING!

The underlying value remains to as many decimal places as Excel is capable of!!!


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DomP]

Skip,

Yes, that's what my second sentence outlines. The formatting/display of the value is different than what is actually used for calculation. Is there a way to combat this? I just can't remember running into this before.

Thanks

 

[SkipVought]

If YOU want a different level of precision, then YOU have to CHANGE THE VALUE accordingly.

So if you are only interested in HUNDRETHS, then you must ROUND or in some other way TRUNCATE the value (quotient).

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DomP]

Skip,

Thanks, I started rounding ,2 from the beginning of the calculation and that seems to be doing the trick. Thanks for all your help with this.

Dom