reading a value from a cell in Excel (& Pivot Tables)

[CR0X]

I am doing some work for our accounts department, and setting up a shed load of pivot tables to add reporting at the end of the month.

As part of this, I need to work out the margin, but Excel works it out differently to us. We calculate profit divided by sales, whereas Excel will straight average, which isn't accurate enough.

If I do the calculation, using the cell references within the report of the Pivot Table, it returns the same figures for each cell, which is frustrating.

Is there a formula where Excel can "read" the result of the cell as a value, regardless of what the formula of the cell is?

[TonyHetherington]

In 2003 it was Tools > options > accuracy as displayed

just looking in 2007 for you

[TonyHetherington]

In 2007, click Microsoft button > Excel Options

Then under "when calculating" tick "set precision as displayed".

That then uses the displayed value rather than the calculated value to make your second calculation.

[CR0X]

thanks for that. Saved a massive headache. Bizarrely, it doesn't work if you Copy Down a formula, so you have to re-create it each time. Not too much of a hassle for 8 rows thankfully.