Formula and text in one cell?
 

I have a formula that SUMS the rows above it but also want to show the
overall percent. Can I do a SUM and text and another SUM in one cell without
additional hidden cells?

Something like this?

=SUM(J5:J20) & "Text here" & SUM((J:20+K:20+L:20)/3)

Formula and text in one cell?
 

Try this. Your posted formula did not need the second sum function or the ":"
.... Otherwise it should work just fine.

=SUM(J5:J20) & " Text here " & (J20+K20+L20)/3
or
=SUM(J5:J20) & " Text here " & sum(J20:L20)/3

--
HTH...

Jim Thomlinson


"HotRod" wrote:

I have a formula that SUMS the rows above it but also want to show the
overall percent. Can I do a SUM and text and another SUM in one cell without
additional hidden cells?

Something like this?

=SUM(J5:J20) & "Text here" & SUM((J:20+K:20+L:20)/3)

Formula and text in one cell?
 

Jim;
Your code for wonderful in the cell. However, I have tried to do apply it in
VBA and the formula did not work.
What I want to type is:
'ActiveCell.FormulaR1C1 = "test &=DEFAULTS!C5 & test"

Could you please help me?

Thanks in advance.
Maperalia


"Jim Thomlinson" wrote:

Try this. Your posted formula did not need the second sum function or the ":"
... Otherwise it should work just fine.

=SUM(J5:J20) & " Text here " & (J20+K20+L20)/3
or
=SUM(J5:J20) & " Text here " & sum(J20:L20)/3

--
HTH...

Jim Thomlinson


"HotRod" wrote:

I have a formula that SUMS the rows above it but also want to show the
overall percent. Can I do a SUM and text and another SUM in one cell without
additional hidden cells?

Something like this?

=SUM(J5:J20) & "Text here" & SUM((J:20+K:20+L:20)/3)

Formula and text in one cell?
 

Maybe...

Option Explicit
Sub testme()

Dim myCell As Range
Dim myStr As String

Set myCell = Worksheets("defaults").Range("c5")

myStr = "=""test ""&" & myCell.Address(external:=True) & "&"" test"""

ActiveCell.Formula = myStr

'or

ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = "Test " & myCell.Text & " Test"

End Sub

But remember, if you use .formulaR1C1, you can't refer to a cell as C5 (r5c3 is
fine).

In R1C1 notation, C5 means Column 5 (E).



maperalia wrote:

Jim;
Your code for wonderful in the cell. However, I have tried to do apply it in
VBA and the formula did not work.
What I want to type is:
'ActiveCell.FormulaR1C1 = "test &=DEFAULTS!C5 & test"

Could you please help me?

Thanks in advance.
Maperalia

"Jim Thomlinson" wrote:

Try this. Your posted formula did not need the second sum function or the ":"
... Otherwise it should work just fine.

=SUM(J5:J20) & " Text here " & (J20+K20+L20)/3
or
=SUM(J5:J20) & " Text here " & sum(J20:L20)/3

--
HTH...

Jim Thomlinson


"HotRod" wrote:

I have a formula that SUMS the rows above it but also want to show the
overall percent. Can I do a SUM and text and another SUM in one cell without
additional hidden cells?

Something like this?

=SUM(J5:J20) & "Text here" & SUM((J:20+K:20+L:20)/3)




--

Dave Peterson

Formula and text in one cell?
 

WORKED GREAT Thanks


"Jim Thomlinson" wrote in message
...
Try this. Your posted formula did not need the second sum function or the
":"
... Otherwise it should work just fine.

=SUM(J5:J20) & " Text here " & (J20+K20+L20)/3
or
=SUM(J5:J20) & " Text here " & sum(J20:L20)/3

--
HTH...

Jim Thomlinson


"HotRod" wrote:

I have a formula that SUMS the rows above it but also want to show the
overall percent. Can I do a SUM and text and another SUM in one cell
without
additional hidden cells?

Something like this?

=SUM(J5:J20) & "Text here" & SUM((J:20+K:20+L:20)/3)