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("VALEU...
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 🙂 )
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.
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 🙂 )
- Hello Jan,
Thank you very much you are a life saver. Wish I knew about this one earlier… Quick little thing, maybe it is because my excel is in French but the x and the cell are inverses for me ( LET(x;A1;x+x)) ‘. Anyway thank you very much!No, that must have been a typo by Jan Karel.
