## Build formula in cell

ErikJan
BronzeLounger
Posts: 1302
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

### Build formula in cell

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
BronzeLounger
Posts: 1302
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

### Re: Build formula in cell

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

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

ErikJan
BronzeLounger
Posts: 1302
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

### Re: Build formula in cell

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
5StarLounger
Posts: 606
Joined: 14 Nov 2012, 16:06

### Re: Build formula in cell

"=sum(10;11;20)"

"=sum(A1;B10;C20)"

"=sum(A1:C1)"

DocAElstein
5StarLounger
Posts: 652
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

### Re: Build formula in cell

ErikJan wrote:
11 Feb 2024, 13:57
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
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

ErikJan
BronzeLounger
Posts: 1302
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

### Re: Build formula in cell

snb wrote:
11 Feb 2024, 21:53
"=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
BronzeLounger
Posts: 1302
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

### Re: Build formula in cell

DocAElstein wrote:
11 Feb 2024, 22:40

_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
Posts: 79127
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Build formula in cell

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``````
Best wishes,
Hans

snb
5StarLounger
Posts: 606
Joined: 14 Nov 2012, 16:06

### Re: Build formula in cell

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
BronzeLounger
Posts: 1302
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

### Re: Build formula in cell

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.
Last edited by ErikJan on 12 Feb 2024, 16:41, edited 1 time in total.

snb
5StarLounger
Posts: 606
Joined: 14 Nov 2012, 16:06

### Re: Build formula in cell

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
BronzeLounger
Posts: 1302
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

### Re: Build formula in cell

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
5StarLounger
Posts: 606
Joined: 14 Nov 2012, 16:06

### Re: Build formula in cell

You are familiar with the switch ?

Code: Select all

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

ErikJan
BronzeLounger
Posts: 1302
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

### Re: Build formula in cell

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
5StarLounger
Posts: 652
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

### Re: Build formula in cell

ErikJan wrote:
12 Feb 2024, 09:06
...
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 )
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

ErikJan
BronzeLounger
Posts: 1302
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

### Re: Build formula in cell

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
5StarLounger
Posts: 1127
Joined: 21 Jan 2011, 16:51
Location: Florida

### Re: Build formula in cell

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.
PJ in (usually sunny) FL

ErikJan
BronzeLounger
Posts: 1302
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

### Re: Build formula in cell

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

DocAElstein
5StarLounger
Posts: 652
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

### non VBA way to have in a single column the result of a multicolumn summation, and the numbers used in the summation form

@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 ).
_.___________________
PJ_in_FL wrote:
13 Feb 2024, 20:30
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
You do not have the required permissions to view the files attached to this post.
Last edited by DocAElstein on 16 Feb 2024, 08:58, edited 1 time in total.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

PJ_in_FL
5StarLounger
Posts: 1127
Joined: 21 Jan 2011, 16:51
Location: Florida

### Re: Build formula in cell

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