How to Avoid Rounding Errors
 

As I understand it Excel displays data according to the Format of a Cell but
calculates using the underlying value. One can get at the value but using
Cell.Value. Please correct this if I'm wrong as I'm no expert! Now I've come
across many cases where this has made it appear that macro's don't sum
correctly ( and in fact in spreadsheets in general ) so other than using
Round() in every calculation ( and I'm assuming here the this will force
Excel to store and use the rounded value from the cell ) is there any way of
saying 'Look Excel all my figures are currency so please round the results of
all your calcs to 2 dp' e.g. £10 / 3 = £3.33 not 3.3333

How to Avoid Rounding Errors
 

Take a look he

http://www.mcgimpsey.com/excel/pennyoff.html

In article ,
"John Pritchard" wrote:

As I understand it Excel displays data according to the Format of a Cell but
calculates using the underlying value. One can get at the value but using
Cell.Value. Please correct this if I'm wrong as I'm no expert! Now I've come
across many cases where this has made it appear that macro's don't sum
correctly ( and in fact in spreadsheets in general ) so other than using
Round() in every calculation ( and I'm assuming here the this will force
Excel to store and use the rounded value from the cell ) is there any way of
saying 'Look Excel all my figures are currency so please round the results of
all your calcs to 2 dp' e.g. £10 / 3 = £3.33 not 3.3333

How to Avoid Rounding Errors
 

Thanks - I'll experiment with using the precision as displayed option. I
guess this means that the .value is stored as 2 d.p. in all cases if I'm
working with currency? I've also heared that there's a .value2 - have you any
idea if this is true or what it is used for.

"JE McGimpsey" wrote:

Take a look he

http://www.mcgimpsey.com/excel/pennyoff.html

In article ,
"John Pritchard" wrote:

As I understand it Excel displays data according to the Format of a Cell but
calculates using the underlying value. One can get at the value but using
Cell.Value. Please correct this if I'm wrong as I'm no expert! Now I've come
across many cases where this has made it appear that macro's don't sum
correctly ( and in fact in spreadsheets in general ) so other than using
Round() in every calculation ( and I'm assuming here the this will force
Excel to store and use the rounded value from the cell ) is there any way of
saying 'Look Excel all my figures are currency so please round the results of
all your calcs to 2 dp' e.g. £10 / 3 = £3.33 not 3.3333