Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[Nickbat]

I can't get my head around this. It all arose as a result of a very simple pie chart I was doing for a client.

Take the following six numbers:
41.00
22.68
11.03
8.56
8.63
7.23

Format the cells as numbers to two decimal places, then total them. It comes out to 99.13

Then reformat the cells to one decimal place:

41.0
22.7
11.0
8.6
8.6
7.2

Excel does the rounding up/down correctly and the total comes to 99.1.

Then reformat the cells to no decimal places:

41
23
11
9
9
7

Excel again rounds up and down correctly and makes the total 99.

HOWEVER, if you add this last set of numbers up (in your head, or on a calculator if you were born after 1970  ), the total is 100.

How can that be?

[STMO123]

I'm no expert at excel Nick, but, simple maths tells me you've added 0.3 to the 22.7 and 0.4 to both the 8.6's which equals........1

[Grumpy]

Because a spreadsheet will always sum the numbers correctly, not just
to the decimal point that you wish it to display.

When it displays the figures, it will round it up or down and
display it to the decimal point that you specified.

In your last column, it has rounded up the figures to display as
whole numbers.

[Entwood]

If you don't format the total line you will see why !!!  although you change the format of each cell you only change it's display .. NOT the content which remains as you  input it .. so the total is rounded on the CONTENT not the display .. it is therefore rounded down to 99  even though each cell is rounded differently.. some up some down

[Golfbuddy]

Just because you change the formatting to 'nil' decimal places doesn't mean that the numbers have changed. In your example, the two 9's are still actually 8.56 and 8.63, which makes 17.19 which when rounded to 'nil' decimal places makes 17 and not 18 which it would in your example and the total still makes 99.13. So the answer is quite simple, when you add the column of numbers it does add up to 99 rounded to two decimal places; it will never total 100.

 

[Nickbat]

Thanks guys.

So, it is possible to provide spreadsheet data which nominally shows an incorrect total. I suppose, if you were smart, you could exploit this feature. But, I'm not smart, so I'll stop thinking about it.  

[STMO123]

It's possible to manipulate any list of figures Nick, just depends whether you choose to round them up...or down

[Entwood]

It has been exploited .... several times  !!

The classic case was quite a few years back in the early days of computing/accountancy, a worker in an american bank realised that interest payments were calculated to 4 decimal places of dollars  ie .. 2 decimal places of cents eg $01.1124 but only paid to accounts as cents and always the bank "rounded down"   so $01.1124 would be paid as $01.11. He wrote a small program to have the other little bits $0.0024 (1/4 of a cent) to be paid into a secret account he controlled. Doesn't sound much but with a few hundred thousand accounts it soon added up to a nice amount.

Urban myth on this is that he was never prosecuted but employed by the bank to look for others trying to do similar  !!  

[Golfbuddy]

Thanks guys.

So, it is possible to provide spreadsheet data which nominally shows an incorrect total. I suppose, if you were smart, you could exploit this feature. But, I'm not smart, so I'll stop thinking about it.  

Good idea. Go back to designing hall cupboards.  

[Nickbat]

Thanks guys.

So, it is possible to provide spreadsheet data which nominally shows an incorrect total. I suppose, if you were smart, you could exploit this feature. But, I'm not smart, so I'll stop thinking about it.  

Good idea. Go back to designing hall cupboards.  

Cheeky git!  

Actually SWMBO was very happy with the cupboard, but demanded I paint the door white as the mahogany clashed with the new pine.  

[Golfbuddy]

Thanks guys.

So, it is possible to provide spreadsheet data which nominally shows an incorrect total. I suppose, if you were smart, you could exploit this feature. But, I'm not smart, so I'll stop thinking about it.  

Good idea. Go back to designing hall cupboards.  

Cheeky git!  

Actually SWMBO was very happy with the cupboard, but demanded I paint the door white as the mahogany clashed with the new pine.  

Would have looked nice in a subtle shade of salmon.  

[Nickbat]

Thanks guys.

So, it is possible to provide spreadsheet data which nominally shows an incorrect total. I suppose, if you were smart, you could exploit this feature. But, I'm not smart, so I'll stop thinking about it.  

Good idea. Go back to designing hall cupboards.  

Cheeky git!  

Actually SWMBO was very happy with the cupboard, but demanded I paint the door white as the mahogany clashed with the new pine.  

Would have looked nice in a subtle shade of salmon.  

I think your angling for a bunch of fives.