How to reveal numbers in formula?

  • Thread starter Thread starter Brett
  • Start date Start date
B

Brett

The following cells have these values:

A1: 2
B1: 3
C1: =(A1+B1)

I'd like to expand C1 with the formula values visible. For example:
C: =(2+3)

How can this be done in Excel 2002?

Thanks,
Brett
 
In cell C1 enter this:


= "(" A1 & "+" & B1 & ")"

Also note that you do not need the braces in your original
formula.

Steve.

Brett wrote
 
Brett

Press the keys CONTROL + Tilde

or

Select Tools | Options - View tab, check the Formulas check box...

or

Run this code:

ActiveWindow.DisplayFormulas = Not ActiveWindow.DisplayFormulas
 
That is not going to give him what he asked for. That will display the formula
and not the values which the formula will use.

Steve.

Charles Maxson wrote
 
Steve,

Is there a quick way to do this without retro fitting the forumulas? I have
quite a few.

Thanks,
Brett
 
How would that look with a formula similar to

=(( C4-C20)*(D4-C21)+(C5-C20)*(D5-C21))/(15-1)

Thanks,
Brett
 
To do this to any formula you just need to put quote marks around
all the parts of your formula which are not cell references and to
use the cell references for the rest. If you want the "=" to be there
too then you need to include it in the formula.

Taking your new example
=(( C4-C20)*(D4-C21)+(C5-C20)*(D5-C21))/(15-1)
Click to expand...


just add a " at the beginning and end of the formula then,
before and after each cell reference add the three characters
" & " and you will get results which look like the formula
you have with numbers instead of cell references.
= " =((" & " C4" & " -" & " C20" & " )*(" & " D4" & " -" & " C21" & " )+(" & " C5" & " -" & " C20" & " )*(" & " D5" & " -" & " C21"
Click to expand...
& " ))/(15-1)"

that should do that new formula.


The thing is, why do you want to do this as I seems a weird
thing to do.

Steve.
 
I need to see which numbers are coming through inside of the formula.

Brett

Steve said:
To do this to any formula you just need to put quote marks around
all the parts of your formula which are not cell references and to
use the cell references for the rest. If you want the "=" to be there
too then you need to include it in the formula.

Taking your new example



just add a " at the beginning and end of the formula then,
before and after each cell reference add the three characters
" & " and you will get results which look like the formula
you have with numbers instead of cell references.
Click to expand...
" )+(" & " C5" & " -" & " C20" & " )*(" & " D5" & " -" & " C21"
 
In that case you may want to add the original formula to the end of that
new formula after inserting an "=" which is within quotes.

EG, you want to add two numbers together so you would use

= A1 + B1

To make this display as you want you would use


= A1 & " + " & B1 " & " = " & A1 + B1

and you will get this displayed when A1 contains a 4 and B1 contains a


4 + 5 = 9


The other way would be to put the real results in the column to the right
of the one you are entering that modified formula into. This way it would
be easy to use those results in another formula in your workbook.


Steve.
 
Not sure if this is what you want. Would the following macro help in
converting the active cell to what you want? It's limited because
"DirectPrecedents" doesn't work referencing cells on other sheets. But
....maybe for something simple...

Sub Demo()
'// Dana DeLouis
Dim s As String
Dim cell As Range

s = ActiveCell.Formula
For Each cell In ActiveCell.DirectPrecedents
s = Replace(s, cell.Address(False, False), cell.Value)
Next cell
ActiveCell.Formula = s
End Sub
 
Back
Top