CODEWITHSUNDEEP
excelexcel-formula
O.Badr
O.Badr

Reputation: 3141

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

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.

Upvotes: 2

Views: 161081

Answers (2)

StephenDonaldHuffPhD
StephenDonaldHuffPhD

Reputation: 958

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)

Upvotes: 9

Tom Sharpe
Tom Sharpe

Reputation: 34380

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

Upvotes: 2

Related Questions