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

Take a look here:

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

In article <[email protected]>,
"John Pritchard" <[email protected]> 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

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 here:
>
> http://www.mcgimpsey.com/excel/pennyoff.html
>
> In article <[email protected]>,
> "John Pritchard" <[email protected]> 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
>