Displaying cell values in formula

[David2]

I would like to display my excel formula with data (not cell references).

Here I've created some visually poor attempt at this.

https://i.imgur.com/JQm9g1g.png

Ideally, Excel would display my formula (with raw data, in a fraction form as shown above) and in another cell show the actual result.

Preferably this would be done without macros.

 

[StephenCrump]

Welcome to the Forum!

Perhaps something along these lines:

E2: ="=(" & TEXT(A2,"$#,##0.00")& " + " &TEXT(B2,"$#,##0.00")&") / "&C2

Book1
	A	B	C	D	E
1	Data			Result	Formula
2	$1.00	$2.50	2	$1.75	=($1.00 + $2.50) / 2
3	$4.00	$5.00	6	$1.50	=($4.00 + $5.00) / 6
Sheet1

 

[David2]

Stephen thanks for the welcome and your help. Worked great!


Just one more question - is it possible to have the E cell display the formula as a "real fraction" (where the dividend is actually situated over the divisor, like in any math book)?

https://imgur.com/a/5L8bM

 

[StephenCrump]

A couple of possibilities:

1. Excel formula (a bit clunky looking):

E2: ="(" & TEXT(A2,"$#,##0.00")& " + " &TEXT(B2,"$#,##0.00")&")"&CHAR(10)&REPT("-",LEN("(" & TEXT(A2,"$#,##0.00")& " + " &TEXT(B2,"$#,##0.00")&")"))&CHAR(10)&C2

2. You need VBA to put more than one format into a cell whose result is determined by formula, so for example:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range, r As Range
    Dim s As String
    
    Set rng = Intersect(Target, Range("MyData"))
    If rng Is Nothing Then Exit Sub
    Application.EnableEvents = False
    
    For Each r In rng.Rows
        s = "(" & Format(Range("A" & r.row).Value, "$#,##0.00") & " + " & Format(Range("B" & r.row).Value, "$#,##0.00") & ")"
        With Range("F" & r.row)
            .Font.Underline = False
            .Value = s & Chr(10) & Range("C2").Value
            .Characters(1, Len(s)).Font.Underline = True
        End With
    Next r
    
    Application.EnableEvents = True

End Sub

F2 and F3 don't display properly below, but the numerators are actually underlined so they look quite neat:

Book1
	A	B	C	D	E	F
1	Data			Result	Formula	VBA
2	$1.00	$2.50	2	$1.75	($1.00 + $2.50) --------------- 2	($1.00 + $2.50) 2
3	$4.00	$5.00	6	$1.50	($4.00 + $5.00) --------------- 6	($4.00 + $5.00) 6
Sheet1

 

[StephenCrump]

Oops, correction:

'This line
.Value = s & Chr(10) & Range("C2").Value
'should be
.Value = s & Chr(10) & Range("C" & r.row).Value

 

[David2]

That looks great! Will try it. Thanks again!