Build formula in cell

[ErikJan]

Let's suppose I have three cells, one with 10, one with 11 and one with 20.

I want to create a formula in a new cell that reads: = 10 + 11 + 20 (and the value of that cell should of course be 31).

Now, I can 'build' the string "= 10 + 11 + 20", that's easy. But how do I go from there?

Sorry, maybe this is super simple and I'm just getting rusty.

PS. Yes, I can copy my text string and past it in a new cell as values and then and hit 'Enter', that works. But I have many cells...

[ErikJan]

Wait... maybe past all as value and then Ctrl-Enter

Update: no, that doesn't work of course...

[ErikJan]

New update:

I turned the string into "'= 10 + 11 + 20" (I placed a ' in front of the = sign).
Then I replaced all '= with = and bingo!

[snb]

"=sum(10;11;20)"

"=sum(A1;B10;C20)"

"=sum(A1:C1)"

[DocAElstein]

Let's suppose I have three cells, one with 10, one with 11 and one with 20.

I want to create a formula in a new cell that reads: = 10 + 11 + 20 (and the value of that cell should of course be 31).

I expect I have missed the point, as usual.

_1 I would have thought that 10 + 11 + 20 should be 41. Perhaps you are working in some advanced computer Base Byte Bit logic system that I don’t know about

_2 I have not quite twigged to what you actually are trying to do, but…

Let’s say you had 10 in cell A1, 11 in cell A2, 20 in cell A3 like this
10
11
20
The formula to add those would be
=A1+A2+A3

Now if you want that same formula in a rectangular range area, this would do it

Code: Select all

Sub Test()  '  https://eileenslounge.com/viewtopic.php?p=314185#p314185
 Let Range("C4:G10") = "=$A$1+$A$2+$A$3"
 Range("C4:G10").Replace What:="$", Replacement:=""
End Sub

Your method sounds interesting as well, but I could not figure out how to do it. – I think in Excel doing things where a leading ' is in a cell can be a bit strange. These for example, do not work for me

Code: Select all

Sub Test2()  '  https://eileenslounge.com/viewtopic.php?p=314185#p314185
 Let Range("C4:G10") = "'= A1 + A2 + A3"
 Range("C4:G10").Replace What:="'", Replacement:=""
End Sub

Sub Test3()  '  https://eileenslounge.com/viewtopic.php?p=314185#p314185
 Let Range("C4:G10") = "'= A1 + A2 + A3"
 Range("C4:G10").Replace What:="'=", Replacement:="="
End Sub

The problem is that the leading ' does not go away
Possibly I have missed the point again about what it is you are actually trying to do.


Alan

[ErikJan]

"=sum(10;11;20)"

"=sum(A1;B10;C20)"

"=sum(A1:C1)"

With respect, but I know how to add numbers in Excel ;-).

I'll respond in another post trying to explain again what I was looking for (but my problem is solved now)

[ErikJan]

_1 I would have thought that 10 + 11 + 20 should be 41. Perhaps you are working in some advanced computer Base Byte Bit logic system that I don’t know about

Yep, 41 of course... sorry for that.

Somehow I seem to have problems explaining what I'm looking for sometimes. I'm sorry for that but upon re-reading my post, at least to me my question is clear still.
Let me make it a bit more generic. I have three columns with numbers (let's says 10,000 rows each) and I need to work with the (10,000) sums of these three columns. Of course I know many ways to sum these and that's done in a split second.
But I want to simplify the sheet by removing the three columns. At the same time however, I want to keep insight in how I came to the sums in each row.
The way to do that is to see how the sums were built in each cell of the SUMs column. Of course I can't use =SUM(A1:C3) as that won't work anymore after I delete columns A, B and C. So if A1 was 10, B1 was 11 and C1 was 20, yes D1 should be 41. The only way to should how I came to 41 after I delete columns A, B and C is to have this formula in D1: =10 + 11 + 20 (and that shows 41).
Does that help?
And yes, of course I can write VBA to do that but I was looking for a non-VBA approach.

So what I ended up doing is putting this formula in D1 (and copied that down):

Code: Select all

= "'=" & A1 & " + " & B1 & " + " & C1

Next I select the cells in the D column and past them on top 'as value'.
That then shows the following text string in D1 (text as I start with a '):

Code: Select all

 '=10 + 11 + 20

Now I do an Excel Replace and replace the string '= with =

That changes the text strings in column D to formula's that now calculate the summed values and e.g. look like this for D1:

Code: Select all

= 10 + 11 + 20

[HansV]

Try this macro:

Code: Select all

Sub CreateFormulas()
    Const FirstRow = 1
    Dim LastRow As Long
    Dim rng As Range
    Dim r As Long
    Dim v As Variant
    Application.ScreenUpdating = False
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Set rng = Range("A" & FirstRow & ":D" & LastRow)
    v = rng.Value
    For r = LBound(v) To UBound(v)
        v(r, 4) = "=" & v(1, 1) & "+" & v(r, 2) & "+" & v(r, 3)
    Next r
    rng.Value = v
    Application.ScreenUpdating = True
End Sub

[snb]

Wouldn't this be sufficient ?

Code: Select all

Sub M_snb()
   With Cells(1).CurrentRegion
      .Offset(, .Columns.Count).Resize(, 1) = "=A1+B1+C1"
    End With
End Sub

or unnecessary complicated:

Code: Select all

Sub M_snb()
   With Cells(1).CurrentRegion
      .Offset(, .Columns.Count).Resize(, 1) = Evaluate(Replace("index(A1:A~+B1:B~+C1:C~", "~", .Rows.Count) & ",)")
    End With
End Sub

[ErikJan]

Yes to both and thank you.

As I stated in my longer reply earlier today "And yes, of course I can write VBA to do that but I was looking for a non-VBA approach."
Also I had intentionally posted this in the Excel forum and not in the VBA space.

[snb]

I wouldn't call https://eileenslounge.com/viewtopic.php ... 94#p314194 a VBA approach.

If the numberformat is "General" the formula "=10+11+20" will return 41

[ErikJan]

Alan misunderstood my problem, hence his non VBA suggestions did not help.

My problem is solved as I indicated.

Number format has nothing to do with this.

Again, I apologize but it seems that I am still unable to explain my problem (but it's a mute point as I found the fix myself)

[snb]

You are familiar with the switch ?

Code: Select all

Sub M_snb()
  ActiveWindow.DisplayFormulas = False
  ActiveWindow.DisplayFormulas = True
End Sub

[ErikJan]

Yes, yes. Thanks for you attempts to help. I know all these things and also this has nothing to do with my problem. Again, I'm sorry that it's still not clear but the problem is solved.

[DocAElstein]

...
Somehow I seem to have problems explaining what I'm looking for sometimes. I'm sorry for that but upon re-reading my post, at least to me my question is clear still.
.....
.......I have three columns with numbers (let's says 10,000 rows each) and I need to work with the (10,000) sums of these three columns. Of course ........

Thanks for coming back to clarify.
I think when you know the answer or when you know what you want, then any explanation that is correct will sound OK to you, and it is, with hindsight. Now that I understand, your first explanation now sounds OK to me too.
But getting the message across in the first place to someone is different, especially when they are a bit slow at getting the point as I am , Lol.
Clever people are usually hopeless teachers, The opposite is often true as well – I am quite a good educator, - being an idiot I have to explain very explicitly, or I confuse myself, Lol
( snb is possibly a genius, or trying to act like one a lot of the time, :) - he can’t seem to explain himself or his solutions to save his life, Lol. Or maybe there is another reason, I don’t know. )

Anyways, Thanks for that explanation
I followed that explanation ,and it worked, just as you said, and I understand now that method that you came up with. That is very interesting, because I had tried very similar things, but for some strange reason the Excel Find and Replace did not work, sometimes giving strange results, but doing it just as you explained does work for me.
So that is useful for future reference.
I will need to go away and think about that solution sometime, as I am interested to figure out why that works as you suggested to give the result via the Excel Find and Replace, whereas trying to do similar Excel Find and Replace with similar characters at the start gave me weird results, ( although this may depend on your Excel versions or settings. I must investigate that sometime )

Alan

( P.S.
If I may be so bold and indulge, I would like to re explain, your method, just for future reference, here It is almost your explanation, just worded in a way that I might remember better. Actually on second read your explanation is OK, but a few different explanations often help get the message across to more people )

[ErikJan]

Thanks, you got it! And yes, the post you made in the forum you point to is indeed what I was looking for but explained much better indeed.

[PJ_in_FL]

ErikJan,

Are your results showing only the components that created the result (41), but the cell shows "= 10 + 11 + 20", but does not show the result?

Maybe I'm betting a deceased equine but unless I see your result I really don't understand what you're spreadsheet looks like at the end of the process.

[ErikJan]

Follow Alan's link to his explanation (it's better than mine). Hopefully that clarifies... ;-)

[DocAElstein]

@PJ
Hi PJ
ErikJan already had a solution he came up with himself. I just took a further look at it out of interest, because it was an unusual interesting solution, ( and it is a bit difficult to explain, for anyone, as it is an unusual solution, and it’s not obvious what is going on, as you have noticed yourself ).
_.___________________

Maybe I'm betting a deceased equine but unless I see your result I really don't understand what you're spreadsheet looks like at the end of the process.

Example… Say he wanted to sum the numbers here, ( so he would be looking for 5 sets of results, 41 44 47 50 53, which for convenience he would want to see in the corresponding ros in column D. But he also wants a non VBA way to keep a record of the numbers used in each of the 5 summations, even if he deletes columns A - C ),

5 rows of numbers to be summed.JPG

Using the method that ErikJan came up with , he could, for example, have his spreadsheet at the end of the process looking like this

What spreadsheet looks like at the end of the process.JPG

, and if he were to hit the back button, he would see this, even if he had deleted the cells containing the numbers used to get those summed results.

After hitting the back button.JPG

Alternatively he could do it the other way around,- leaving the spreadsheet showing the formulas so that he could see the numbers used in the summation, and then use his method to show the summed result, and once again, this works even if he had deleted the cells containing the numbers used to get those summed results.

( My extended explanations are here )
Alan

[PJ_in_FL]

OK, so I guess I was confused how complicated you made it when you still ended up with results that required modification, which you then had to select all of the results and enter a ctrl-h dialog to complete a substitution.

It seemed the simple equation of "=CONCAT("= ",A2," + ",B2," + ",C2)" (without the quotes) gets the result of "= 10 + 11 + 20", then, since you are going to select all the cells anyway, select the cells and ctrl-c, select top cell in the column and alt-e-s-v, enter and you're done.


At first I thought you wanted to also include the result in the column, and the only ways I could see to do that were to use VBA:

1) Create a custom format for each cell ...

Code: Select all

Sub show_formula()
Dim c As Range
Dim va As String
Dim vb As String
Dim vc As String
Dim sFormat As String

For Each c In Selection
    va = CStr(c.Offset(0, -3).Value)
    vb = CStr(c.Offset(0, -2).Value)
    vc = CStr(c.Offset(0, -1).Value)
    sFormat = Chr(34) & va & " + " & vb & " + " & vc & " = " & Chr(34) & "0"
    c.NumberFormat = sFormat
    c.Value = c.Value
Next

End Sub

... which gives the result that displays "10 + 11 + 20 = 41" in the cell when the results is 41, but which has the disadvantage of creating a custom format for each cell, and you mentioned having thousands (I don't know Excel's limitation).

Alternatively, another method is shown below:

2) Create a custom comment for each cell, so the integrity of the database is maintained, and the results are easily seen, can be filtered, sorted, etc., with the values used to obtain the result is still visible for inspection via the cell comment.

Code: Select all

Sub show_formula_in_comment()
Dim c As Range
Dim va As String
Dim vb As String
Dim vc As String
Dim sFormat As String

On Error Resume Next

For Each c In Selection
    va = CStr(c.Offset(0, -3).Value)
    vb = CStr(c.Offset(0, -2).Value)
    vc = CStr(c.Offset(0, -1).Value)
    sFormat = va & " + " & vb & " + " & vc & " = "
    c.Comment.Delete
    c.AddComment (sFormat)
    c.Value = c.Value
Next

End Sub

I guess my methods are not as concise as SNB, but I haven't a clue how to decode that bit of programming anyway. Reminds me of one liner APL code and it's esoteric symbols.

Oh, and I was BEATING the above, not BETTING, although considering the aftermath of some bets I've made on certain equines I swore afterwards they were deceased.