Excel show formulas WITH NUMBERS
Closed
ElWapp
-
Mar 22, 2012 at 02:48 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 24, 2012 at 02:14 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 24, 2012 at 02:14 PM
Hello,
I have a problem with making Excel write the formula with actual numbers.
This is the text I want to get:
A=1
B=3
C=A+B=1+3=4
In the third line, I'd like to write the formula only once and see the formula in one cell, the formula with actual numbers in second and the result in the third cell. I can write out the formula (C=A+B=4), but can't figure out how to show the numbers used.
Thanks for any tips.
I have a problem with making Excel write the formula with actual numbers.
This is the text I want to get:
A=1
B=3
C=A+B=1+3=4
In the third line, I'd like to write the formula only once and see the formula in one cell, the formula with actual numbers in second and the result in the third cell. I can write out the formula (C=A+B=4), but can't figure out how to show the numbers used.
Thanks for any tips.
Related:
- Excel show formulas WITH NUMBERS
- Number to words in excel formula - Guide
- Excel grade formula - Guide
- Excel mod apk for pc - Download - Spreadsheets
- Date formula in excel dd/mm/yyyy - Guide
- Excel spreadsheet formulas - Guide
1 response
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Mar 24, 2012 at 08:43 AM
Mar 24, 2012 at 08:43 AM
For that you have to write a custom function first. Then use that function on your sheet
1. Open workbook
2. Press Alt + F11
3. Click on insert and add a new module
4. paste the code below the instructions
5. Call the function on sheet as given in the sample call
1. Open workbook
2. Press Alt + F11
3. Click on insert and add a new module
4. paste the code below the instructions
5. Call the function on sheet as given in the sample call
'This functions returns the formula written in a cell
'if more than once cell is passed as parameter, it will return **Error**
'formulaCell : cell which as the formula
' Sample Call : =displayFormula(c1)
Public Function displayFormula(formulaCell As Range) As String
Dim result As String
If formulaCell.Count <> 1 _
Then
result = "**Error**"
Else
result = formulaCell.Formula
End If
displayFormula = result
End Function
Mar 24, 2012 at 09:07 AM
Mar 24, 2012 at 02:14 PM
'This functions returns the value by evaluating a formula written in a cell 'if more than once cell is passed as parameter, it will return **Error** 'formulaCell : cell which has the formula ' Sample Call : =displayFormulaValue(c1) where C1 would have something like this ' =A1 & " + " & b1 Public Function displayFormulaValue(formulaCell As Range) As String Dim result As Variant Dim vPosEqual As Variant Dim vPosAmpersand As Variant If formulaCell.Count <> 1 _ Then displayFormulaValue = "**Error**" Else result = formulaCell.Formula Do result = LTrim(result) vPosEqual = InStr(1, result, "=") If (vPosEqual > 0) _ Then result = Mid(result, vPosEqual + 1) End If Loop While (vPosEqual > 0) displayFormulaValue = Evaluate("=" & result) End If End Function