Sometimes, we format our values in a cell, however, Excel does not consider that while calculating. We can make Excel calculate differently. In this article, we will show you 3 quick methods of Excel return value of cell not formula.
Return Value of Cell Not Formula in Excel: 3 Ways
To demonstrate our methods, we have picked a dataset consisting of 5 columns: Product, Pound, Kilogram, Unit, and Total. Basically, there are 6 products in our dataset given in pounds and we are converting it to kilograms.
Afterward, we multiply it by the number of units, therefore we get the total product weights. We can also see here that the numbers are in one decimal place but there are eight digits after decimal in the output.
Our aim here is to make Excel take the display value from column D and make calculations based on that. For example, the value of cell D5 is 3.2, and multiply it by 13 to get 41.6 as the output instead of 41.27732922.
1. Using ROUND Function to Return Value of Cell Not Formula in Excel
For the first method, we will use the ROUND function to return the value of the cell not the formula in Excel. Before jumping on the steps, let us see how our dataset is defined. 1 kilogram equals 2.2046 pounds. Hence, we have divided it by that number to convert it.
Now, if we multiply the weight by the number of units, then we will get a different output from our intention. We want the output to be 41.6, however, we will get 41.27732922 as the output.
Steps:
- First, select the cell range F5:F10.
- Next, type the following formula.
=ROUND(D5,1)*E5
Formula Breakdown
- First, The ROUND function returns rounded numbers up to a certain decimal place.
- Here, we are rounding the value from cell D5 to the first decimal place.
- So, 7 divided by 2.2046 will be 3.2.
- Lastly, we multiplied it by the number of units sold.
- Therefore, we will get our desired output of 41.6.
- Finally, press CTRL+ENTER.
This will autofill the formula to the rest of the cells. Thus, we have returned the value of the cell not the formula in Excel.
2. Use of Combined Formula to Return Value of Cell Not Formula
For the second method, we will use the TEXT, REPT, RIGHT, and CELL functions to create a combined formula. Then, using this formula we will return the value of the cell, not a formula. Without further ado, let us show you the steps.
Steps:
- First, select the cell range F5:F10.
- Next, type the following formula.
=E5*TEXT(D5,"#."&REPT(0,RIGHT(CELL("format",D5),1)))
Formula Breakdown
- First, Our formula has several parts. The main part of the function is the TEXT function. This function takes the cell contents as it is.
- RIGHT(CELL(“format”,D5),1)
- Output: “1”.
- The CELL function returns the characteristics from a cell in Excel. Here, we have defined the format property of cell D5. So, we will get the output F1 from that, which means numbers after one decimal place.
- Then, the RIGHT function works. This returns the first string from the previous output from the right side. Therefore, we will get the number of decimal places by using this formula combination.
- Then our formula reduces to -> E5*TEXT(D5,”#.”&REPT(0,”1″))
- Output: 41.6.
- The REPT function repeats a value. We have set it to repeat 0, exactly 1 time. Then our TEXT function kicks in and sets our value from cell D5 to take one decimal point. Lastly, using this value we multiply it by the units.
- Finally, press CTRL+ENTER.
This will autofill the formula to the rest of the cells. Thus, we have shown you yet another formula to return the value of the cell not the formula in Excel.
3. Applying Precision as Displayed Feature to Return Value of Cell
For the last method, we will turn on the Set precision as displayed feature to achieve our objective in this article. We have already multiplied to get the total weights of our products. When we enable the feature, these values will change automatically.
Steps:
- To begin with, press ALT, F, then T to bring up the Excel Options window.
- Next, from the Advanced tab >>> under the When calculating this workbook: section >>> select Set precision as displayed.
- Then, press OK.
- A warning message will appear, press OK.
- After this, it will change our values.
- In conclusion, we have shown you the last method of returning the value of a cell not a formula in Excel.
Read More: Convert Formula to Value in Multiple Cells in Excel
Practice Section
We have added a practice dataset for each method in the Excel file. Therefore, you can follow along with our methods easily.
Download Practice Workbook
Conclusion
We have shown you 3 quick methods to Excel return value of cell not formula. If you face any problems regarding these methods or have any feedback for me, feel free to comment below.
Related Articles
- How to Stop Formula to Convert into Value Automatically in Excel
- How to Convert Formula to Value Automatically in Excel
- How to Convert Formula Result to Text String in Excel
- Excel VBA: Convert Formula to Value Automatically
- Convert Formula to Value Without Paste Special in Excel
- Putting Result of a Formula in Another Cell in Excel
<< Go Back to Convert Formula to Value in Excel | Excel Formulas | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Hello Rafiul,
Got a problem with the IF function not returning a time value hh:mm but either returning the formula or the cell number where I tried to use a workaround.
Problem:
1. Lock off 23:15
2. Lock on 04:15
3. Lock off 05:05
4. Lock 0n 07:00
Total time is the difference between 1. and 4. but if 3. and 4. did not take place the Total time is between 1. and 2.
=IF(MOD(B43-B30,1))>(MOD(B35-B30,1)),“MOD(B43-B30,1)”,“MOD(B35-B30,1)”)
Dear Peter Summers
Thanks for visiting our blog and sharing your problem. You wanted to return a time value with “hh:mm”. To do so, you have to enhance your formula with the help of the TEXT function:
Hopefully, you have found the idea helpful; good luck.
Regards
Lutfor Rahman Shimanto
ExcelDemy