How to reveal numbers in formula?

[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

 

[Steve]

In cell C1 enter this:


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

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

Steve.

Brett wrote

 

[Charles Maxson]

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

 

[Steve]

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

 

[Brett]

Steve,

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

Thanks,
Brett

 

[Brett]

How would that look with a formula similar to

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

Thanks,
Brett

 

[Steve]

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)

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"

& " ))/(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.

 

[Brett]

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

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



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.

" )+(" & " C5" & " -" & " C20" & " )*(" & " D5" & " -" & " C21"

 

[Steve]

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.

 

[Dana DeLouis]

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