Restrict the number of decimal places that excel uses 1

[sapatos]

Hi,

I've a massive spreadsheet with many sheets in it. Each sheet contains many values and formulas generating various calculations.

My problem is that excel uses many decimal places and bases its calculations on these values (obviously), however I need to restrict excel to only use 7 decimal places, i.e. it must not do anything with a greater precision than this.

I initially tried changing how it displays the numbers however it became obvious that even though I could only see 7 it was still holding a greater precision in the background and using this precision for the formula. This is causing very small but significant variations in the expected output of the formula.

I've also had a look at "fixed decimal places", but this gets overridden if your value has a decimal place in it. Most of my values do have decimal places.

Could someone suggest a method to prevent excel from storing > 7 decimal places. Also I would need to apply this change globally to many sheets so if you can advise how to do that that would be great.

Many thanks

 

[xlbo]

Set ALL of your cells to have 7 decimal places and then go

Tools>Options>Precision as displayed

This will make calculations work only on what is displayed int he cell. As long as all your cells are set to 7 dps then this should work for you.

One major warning however - this will physically change the data in your spreadsheet so I would take a copy 1st in case you need to get the greater detail back

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[sapatos]

works a treat thanks.

 

[macropod]

Note also that the 'Precision as Displayed' setting affects all worksheets and all workbooks until you change it back again - and then it stops applying to the workbook/sheet you're concerned with. It also has no effect on the workbook/sheet when someone else opens it without setting the 'Precision as Displayed' option.

The other thing to note is that the 'Precision as Displayed' setting will mean that a decimal value that is displayed as a whole number, or with less than 7 numbers after the decimal point, will be treated as if those extra decimals didn't exist.

The alternative is to add a rounding function to all your values and formulae, with the precision set to 7 decimal places. That way, you'll get the full 7-point effect, regardless of how the values are displayed. You could add the rounding to selected cells via a macro coded along the lines of:

Sub AddRoundTo7Places()
Dim UnRoundedCell As Object
Dim CellString As String
  For Each UnRoundedCell In Selection
    CellString = UnRoundedCell.Formula
    If IsNumeric(UnRoundedCell) And UnRoundedCell.Value <> "" Then
      If Left(CellString, 1) = "=" Then CellString = Right(CellString, Len(CellString) - 1)
      CellString = "=ROUND(" & CellString & ",7)"
      UnRoundedCell.Formula = CellString
    End If
  Next UnRoundedCell
End Sub

Cheers

[MS MVP - Word]

 

[sapatos]

Thanks for the additional input. Can you clarify that:

"The other thing to note is that the 'Precision as Displayed' setting will mean that a decimal value that is displayed as a whole number, or with less than 7 numbers after the decimal point, will be treated as if those extra decimals didn't exist."

Does that mean that the following won't be included in calculations:

1.0
1.123456

So if they were included in a calculation they would be ignored completely?

so...: 1.1234567 + 1.123456 + 1.0 = 1.1234567 ?

 

[macropod]

Hi sapatos,

'Precision as Displayed' means exactly that - if the number is displayed with no decimals (even if its a 15-digit floating point value), then all calculations depending on that number will treat it as a whole number. Similarly, a number displayed with two digits after the decimal point will be treated as a two-digit decimal, even if ther's more digits that aren't displayed.

Cheers

[MS MVP - Word]