Forum Discussion
Solved
How to have a formula evaluating its own result.
Hello,
In order to do statistics I made this formula (#1):
=(SUBSTITUE(SUBSTITUE("=SUBSTITUE(GAUCHE(CELLULECHOISIE;TROUVE(¶;¶;CELLULECHOISIE)-1);¶[¶;¶¶)";"¶";"""");"CELLULECHOISIE";SUBSTITUE("VALEUR.EN.TEXTE(C20)";"C20";CELLULE("adresse";C21))))
Basically, this formula creates a new formula based on the cell selected (C21 in this case) resulting to:
=SUBSTITUE(GAUCHE(VALEUR.EN.TEXTE($C$21);TROUVE(";";VALEUR.EN.TEXTE($C$21))-1);"[";"")
So, if in a cell (A1) I enter the formula #1, and select the cell C21, the visible result in A1 will be formula 2. This second formula, once copy pasted on an other cell gives a result depending of what C21 contains. The probleme is: I'd like to make it so that the result from formula 2 appears in the same cell I put formula 1 (A1 in this case).
In other words, I need 1 fonction that evaluates itself two times. Exemple: this function:
="=1+1"
Gives as result a new formula:
=1+1
And this formula gives a new result 2. These two steps need to be in the same cell.
Any idea how to do that, with VBA maybe?
Thank you,
Mel
Melvyn1 You can easily do this by wrapping your current formula in the LET function. Lets take your example formula:
=C20 + C20 * C20 + C20
Change it to this:
=LET(x,C20,x + x * x + x)###EDITED: Reversed the X and C20 to fix a typo (actually, I wasn't thinking 🙂 )
6 Replies
- This sounds rather convoluted, can you try to explain what it is you are trying to do? Perhaps there is a simpler way.
- Hello Jan,
I am trying to make a formula that only needs to select one cell one time to do the calculation (so I can modify easier). In my main formula, the cell I wish to use is mentioned two time, but this is only the start of a formula (it will probably be 10x longer), which is why I need to be able to select a cell one time, otherwise it is hell to change. What I did is take the formula in text and change every C20 that are in by 1 desired cell.
It allows to change every places in the formula the cell is mentioned by only mentioning it one time (It is the substitute of every C20 by the cell C21)
So if I have a long formula where C20 is mentioned 10 times, but I wanna change it to C21, by simply substituting “C20” in the formula by C21, the given answer is the written formula with 10x C21. Exemple (this one could be simplified but mine won’t be able to): my main formula is “=C20 + C20 * C20 + C20”
I want to change it to C21 but I need to move the colored cell (when you type the formula, every cells mentioned are colored and you are able to move this colored rectangle). So I do =SUBSTITUE(“the formula”;”C20”;”C21”) the C21 is actually an other formula that allowed me to manually select the cell instead of typing it). But the result is the first formula, in text : “=C21 + C21 * C21 + C21” in stead of the result of this new formula, which is a number. So, I need to evaluate the evaluation of the substitute of my formula, kind of like if I had two time “=“ in one formula.
I know it might be hard to understand and that I may not explain it perfectly, but do not hesitate to ask me again if I am not clear.
Thank you very much for the time, it is really appreciated.Melvyn1 You can easily do this by wrapping your current formula in the LET function. Lets take your example formula:
=C20 + C20 * C20 + C20
Change it to this:
=LET(x,C20,x + x * x + x)###EDITED: Reversed the X and C20 to fix a typo (actually, I wasn't thinking 🙂 )
