Forum Discussion
calculate value based on displayed value
I have the following formula in cell C18
=C16*C19/10
Cell C16 contains the value 2.37 and Cell C19 contains the value 80.
Cell C18 displays the value as 19.0, but the actual value is 18.96. So excel is rounding.
I need to perform the calculation of taking =E18/C18*100. Where E18 contains the value 2.0.
Currently, when I calculate =E18/C18*100 I am getting 10.5485 because excel is calculating based on the actual value, but I need the calculation to be performed on the displayed value of 19.0. So I need the calculation to display 10.5263.
Does anyone know how I might get the calculation to render based on the displayed value and not the actual value? Thanks!
4 Replies
This is a feature in Excel.
- From the top menu select File
- From the bottom left select option, this opens the Excel Options window
- From the left select advanced
- Scroll down almost to the bottom, Look for the "Set Precision as Displayed" check box.
Excel will now use the displayed number when calculation. Example in A1 cell put =10/3 and set the display to show two digits after the decimal place it will show 3.33. Now in cell B1 put =A1*3 the anser will be 9.99.
This option is simple and efficient! You do not have to use Round() to round every cell and every data!
- There are a few options, one option is to use ROUND( x, 1) to force the value to round to 1 decimal place. you can use that in cell C18 = ROUND(C16*C19/10 , 1) and then the VALUE in C18 will actually be 19.0 or you can use it in the "next" cell like =E18/ ROUND(C18,1) * 100
