Build formula in cell
-
- BronzeLounger
- Posts: 1381
- 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...
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...
-
- BronzeLounger
- Posts: 1381
- 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...
Update: no, that doesn't work of course...
-
- BronzeLounger
- Posts: 1381
- 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!
I turned the string into "'= 10 + 11 + 20" (I placed a ' in front of the = sign).
Then I replaced all '= with = and bingo!
-
- 5StarLounger
- Posts: 677
- Joined: 14 Nov 2012, 16:06
Re: Build formula in cell
"=sum(10;11;20)"
"=sum(A1;B10;C20)"
"=sum(A1:C1)"
"=sum(A1;B10;C20)"
"=sum(A1:C1)"
-
- 5StarLounger
- Posts: 767
- Joined: 18 Jan 2022, 15:59
- Location: An Englishman, illegally re-routing rivers, in Hof, Beautiful Bavaria. Rule, Britannia!
Re: Build formula in cell
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
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
Possibly I have missed the point again about what it is you are actually trying to do.
Alan
Regards , Ālan , DocÆlstein
, 


-
- BronzeLounger
- Posts: 1381
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
-
- BronzeLounger
- Posts: 1381
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: Build formula in cell
Yep, 41 of course... sorry for that.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
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
That then shows the following text string in D1 (text as I start with a '):
Code: Select all
'=10 + 11 + 20
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
-
- Administrator
- Posts: 80078
- 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
Hans
-
- 5StarLounger
- Posts: 677
- Joined: 14 Nov 2012, 16:06
Re: Build formula in cell
Wouldn't this be sufficient ?
or unnecessary complicated:
Code: Select all
Sub M_snb()
With Cells(1).CurrentRegion
.Offset(, .Columns.Count).Resize(, 1) = "=A1+B1+C1"
End With
End Sub
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
-
- BronzeLounger
- Posts: 1381
- 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.
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.
-
- 5StarLounger
- Posts: 677
- 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
If the numberformat is "General" the formula "=10+11+20" will return 41
-
- BronzeLounger
- Posts: 1381
- 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)
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)
-
- 5StarLounger
- Posts: 677
- 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
-
- BronzeLounger
- Posts: 1381
- 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.
-
- 5StarLounger
- Posts: 767
- Joined: 18 Jan 2022, 15:59
- Location: An Englishman, illegally re-routing rivers, in Hof, Beautiful Bavaria. Rule, Britannia!
Re: Build formula in cell
Thanks for coming back to clarify.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 ........
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 )
Regards , Ālan , DocÆlstein
, 


-
- BronzeLounger
- Posts: 1381
- 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.
-
- 5StarLounger
- Posts: 1165
- 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.
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
-
- BronzeLounger
- Posts: 1381
- 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... ;-)
-
- 5StarLounger
- Posts: 767
- Joined: 18 Jan 2022, 15:59
- Location: An Englishman, illegally re-routing rivers, in Hof, Beautiful Bavaria. Rule, Britannia!
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 ).
_.___________________
Using the method that ErikJan came up with , he could, for example, have his spreadsheet at the end of the process looking like this
, 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.
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
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 ).
_.___________________
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 ),
Using the method that ErikJan came up with , he could, for example, have his spreadsheet at the end of the process looking like this
, 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.
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.
Regards , Ālan , DocÆlstein
, 


-
- 5StarLounger
- Posts: 1165
- 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 ...
... 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.
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.
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
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
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.
PJ in (usually sunny) FL