Calculate with "Precision as Formatted"

[Tom Lewis]

I am using custom formatting to display data to the
nearest thousand with a format string like:

_(#,",000"_);(#,"000");_("-"_)

This works fine, but I would also like to be able to refer
to the displayed value of cells formatted in this way
rather than the full-precision value of the cell.

I know that I can create formulas that round to thousands
or other precisions, but I was hoping for a simpler
approach, as I would not want to manually synchronize the
precision of applied formats with the related formulas
that calculate the equivalent precisions.

Likewise, I do not want to use "Precision as displayed"
since I also need to be able to obtain the full accuracy
value at times, (plus other worksheets would also be
affected).

Can anyone think of a tidy solution to accomplish what I
want?

Much appreciated,

Tom

 

[Tom Ogilvy]

You can't have it both ways. You either have to tell Excel what you want or
accept what it provides. So, you either loose the greater accuracy and do
your calculations, or you modify your formulas to produce the precision that
you want to work with and retain the greater accuracy. Or, you could write
custom functions that work with the displayed value of the cells, but this
doesn't sound very practical to me unless the operations your do are very
limited.

Regards,
Tom Ogilvy