How to reference Value instead of formula in a formula using Excel?

2
votes

In Microsoft Excel 2010/2013, I have five data columns as follows :

           | A  | B |  C   | D  |  E  |
          ------------------------------
  line 1   |  12|  7|  1.71|   5| 8.57|



C1: it's a formula ==> A1/B1, and it's formatted as two decimal places.

However, the formula of E1 (which is C1*D1) gives 8.57 instead of 8.55, I figured out that Excel uses C1's formula instead of the displayed value, which is not my expectation!

So how can I enforce Excel to use the value(displayed) of a cell instead of its formula, to calculate another formula?

Note: I can get around by using the following formula in E1:

ROUND(C1,2)*D1

...but I want a solution which gets the displayed value directly without formatting the C1 twice.

2
excelexcel-formula
You can also do in C1 =ROUND(A1/B1,2) - Scott Craner
actually I like scott's answer quite a bit on this one. Round your results to what you are going to display. - Forward Ed
You could also set the workbook to use "Precision as Displayed" if you can deal with the other side-effects - Ron Rosenfeld

2 Answers

6
votes

This is the access to a cell's contents, since your original question asked how to access the value rather than the formula that generates the value:

=CELL("contents",$C$1)
2
votes

I guess this is the actual answer to the question:-

=D1*VALUE(TEXT(C1,"#."&REPT(0,RIGHT(CELL("format",C1),1))))

i.e. use the CELL function to find out how many places the number is formatted to then create a format with this number of decimal places and use it in a TEXT function to get the number in C1 to the number of places it is displayed as.

Whether you would ever want to do this is of course another matter.

CELL has the drawback that if you change the formatting of the number in C1, it doesn't update until you recalculate the spreadsheet.

VALUE can be omitted because the multiplication will coerce the string "1.71" to a number:-

=D1*TEXT(C1,"#."&REPT(0,RIGHT(CELL("format",C1),1)))

enter image description here