Apply any formula n times without using VBA? [closed]
Clash Royale CLAN TAG#URR8PPP
I have the following cells:
A1:
justsometext
B1:
3
C1:
=DOSOMETHING(A1)
I want to apply the formula in C1
n times (n being 3, the value in B1
), so in this case it would mean:
C1:
=DOSOMETHING(DOSOMETHING(DOSOMETHING(A1)))
Is there any possibility to do this without a macro, maybe by using array formulas?
UPDATE:
The number of repetitions will not always be 3, but will change over time and/or differ from line to line.
Here is a simple example of what it should look like:
Please note that the solution should work for any formula, and not just for appending a constant string like in the example.
microsoft-excel worksheet-function
closed as too broad by Rajesh S, bertieb, fixer1234, agtoever, G-Man Dec 15 at 20:05
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
I have the following cells:
A1:
justsometext
B1:
3
C1:
=DOSOMETHING(A1)
I want to apply the formula in C1
n times (n being 3, the value in B1
), so in this case it would mean:
C1:
=DOSOMETHING(DOSOMETHING(DOSOMETHING(A1)))
Is there any possibility to do this without a macro, maybe by using array formulas?
UPDATE:
The number of repetitions will not always be 3, but will change over time and/or differ from line to line.
Here is a simple example of what it should look like:
Please note that the solution should work for any formula, and not just for appending a constant string like in the example.
microsoft-excel worksheet-function
closed as too broad by Rajesh S, bertieb, fixer1234, agtoever, G-Man Dec 15 at 20:05
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
3
Do you have a specific formula in mind you're trying to run multiple times? This sounds a lot like a potential XY Problem. Can you briefly describe what you're trying to accomplish/*why* to run the formula three times? In a way, what you used as an example (=value&"_checked"
) would have a different way than if you wanted to run anIndex/Match
three times or so...
– BruceWayne
Dec 12 at 16:41
add a comment |
I have the following cells:
A1:
justsometext
B1:
3
C1:
=DOSOMETHING(A1)
I want to apply the formula in C1
n times (n being 3, the value in B1
), so in this case it would mean:
C1:
=DOSOMETHING(DOSOMETHING(DOSOMETHING(A1)))
Is there any possibility to do this without a macro, maybe by using array formulas?
UPDATE:
The number of repetitions will not always be 3, but will change over time and/or differ from line to line.
Here is a simple example of what it should look like:
Please note that the solution should work for any formula, and not just for appending a constant string like in the example.
microsoft-excel worksheet-function
I have the following cells:
A1:
justsometext
B1:
3
C1:
=DOSOMETHING(A1)
I want to apply the formula in C1
n times (n being 3, the value in B1
), so in this case it would mean:
C1:
=DOSOMETHING(DOSOMETHING(DOSOMETHING(A1)))
Is there any possibility to do this without a macro, maybe by using array formulas?
UPDATE:
The number of repetitions will not always be 3, but will change over time and/or differ from line to line.
Here is a simple example of what it should look like:
Please note that the solution should work for any formula, and not just for appending a constant string like in the example.
microsoft-excel worksheet-function
microsoft-excel worksheet-function
edited Dec 12 at 13:29
robinCTS
3,99941527
3,99941527
asked Dec 12 at 11:48
Scripter22
392
392
closed as too broad by Rajesh S, bertieb, fixer1234, agtoever, G-Man Dec 15 at 20:05
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
closed as too broad by Rajesh S, bertieb, fixer1234, agtoever, G-Man Dec 15 at 20:05
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
3
Do you have a specific formula in mind you're trying to run multiple times? This sounds a lot like a potential XY Problem. Can you briefly describe what you're trying to accomplish/*why* to run the formula three times? In a way, what you used as an example (=value&"_checked"
) would have a different way than if you wanted to run anIndex/Match
three times or so...
– BruceWayne
Dec 12 at 16:41
add a comment |
3
Do you have a specific formula in mind you're trying to run multiple times? This sounds a lot like a potential XY Problem. Can you briefly describe what you're trying to accomplish/*why* to run the formula three times? In a way, what you used as an example (=value&"_checked"
) would have a different way than if you wanted to run anIndex/Match
three times or so...
– BruceWayne
Dec 12 at 16:41
3
3
Do you have a specific formula in mind you're trying to run multiple times? This sounds a lot like a potential XY Problem. Can you briefly describe what you're trying to accomplish/*why* to run the formula three times? In a way, what you used as an example (
=value&"_checked"
) would have a different way than if you wanted to run an Index/Match
three times or so...– BruceWayne
Dec 12 at 16:41
Do you have a specific formula in mind you're trying to run multiple times? This sounds a lot like a potential XY Problem. Can you briefly describe what you're trying to accomplish/*why* to run the formula three times? In a way, what you used as an example (
=value&"_checked"
) would have a different way than if you wanted to run an Index/Match
three times or so...– BruceWayne
Dec 12 at 16:41
add a comment |
6 Answers
6
active
oldest
votes
Then I would use = Value & REPT("_checked", NoOfExecutions)
.
If you always need to do stuff like the one on the example (concatenating strings), it works pretty well.
If you need to use other formulas, this is what I can think of:
we separate the function you will need to use: beginning (everything that should go before the main argument) and end (anything that follows the argument, including additional arguments). For example if we use the function
LEFT(value, 2)
,LEFT(
will go in the Beginning,, 2)
in the End.we build the formula as text with concatenation and
REPT
. Referring to the example in the picture, formula in cell C6 will be:= "=" &REPT($B$2,B6) & $A6 & REPT($B$3,$B6)
Then you need to copy the cell and paste it as values in cell D6; then click on the formula in the formula bar and press Enter on your keyboard.
It's a few steps but it avoids VBA.
Pleas read the clarification I added to the end of the question. A general solution is required.
– robinCTS
Dec 12 at 13:32
Thanks for your help, robinCTS; I was just about to write something similar. What I am looking for is a general solution. But nice to have this very easy solution for string concatenation. Thanks, VFor.
– Scripter22
Dec 12 at 13:52
@Scripter22 You're welcome. FYI, you need to prepend an@
to a username in order for them to get pinged if they aren't the poster of the question/answer you are commenting on. See How do comment @replies work? for the full details.
– robinCTS
Dec 12 at 14:03
Nice try for a general solution! It doesn't quite work as the OP intends, though. The idea is to allow n to be changed and have the result automatically update. However, the main problem is that this only works for a limited number of formulas where "Value" only occurs once. (For example, the simple formula=LEFT(A1,LEN(A1)-1)
can't be expanded at all by usingREPT()
.)
– robinCTS
Dec 12 at 14:45
add a comment |
No, sorry, this is not possible in the general case for any formula plus
having it update automatically. Without using VBA, that is.
However,it can be done for a very small number of specific formula (like concatenating a constant string). It can also be done, but with manual updating, for a certain set of formulas as cleverly shown in VFor's answer.
The closest you can get to a general solution is to rearrange the cells, embed the DOSOMETHING
formula in a special wrapper formula, and use helper columns.
For your supplied example worksheet:
Rearrange it like this:
Enter the following formula in D2
and ctrl-enter/copy-paste/fill-down&right/auto-fill into the rest of the table's columns:
=IF(COLUMN()-COLUMN($C2)>$A2,"§",C2&"_checked")
Enter the following formula in B2
and ctrl-enter/copy-paste/fill-down/auto-fill into the rest of the table's column:
=INDEX(C2:INDEX(2:2,1,COLUMNS(2:2)),MATCH("§",C2:INDEX(2:2,1,COLUMNS(2:2)),0)-1)
Note that the number of helper columns required is the maximum allowable value of n plus one. If there aren't enough of them for an entered value, an error ensues:
Explanation:
The generalised wrapper formula for the helper columns is:
=IF(COLUMN()-COLUMN($C2)>$A2,"§",DOSOMETHING(C2))
where DOSOMETHING(C2)
is any formula based on C2
only (for example, LEFT(C2,LEN(C2)-1)
which progressively removes the last character).
The wrapper formula works by operating on the cell to the left, thus effectively "nesting" the formulas the further to the right in the row it goes.
The IF(COLUMN()-COLUMN($C2)>$A2,"§",
part uses the column indexes to count down the number of times the DOSOMETHING
formula is nested, and once the number of times specified in column A
has been achieved it outputs terminator strings. These strings do not necessarily need to be §
. They just need to be something that will never be the result of the evaluation of any number of nested formulas for any allowable Value
.
The Result
formula looks trickier. However, the C2:INDEX(2:2, 1, COLUMNS(2:2))
parts are simply the sub-range of row 2
to the right of the Result
column.
The formula is thus essentially the same as:
=INDEX(2:2,MATCH("§",2:2,0)-1)
which makes it easier to understand.
(Note that this formula actually works if iterative calculations are enabled.)
Looking at this simpler formula, it is clear that the formula returns the n-level nested DOSOMETHING
function result.
I think I just wrote an answer similar to this, but slightly different. I think your answer is easier to copy paste across the matrix of helper columns if the number of possible iterations is very large. My answer might be easier to implement if the iteration maximum is fairly small. Brilliant answer!
– Todd Wilcox
Dec 12 at 19:43
add a comment |
It can be done via the Evaluate and Rept functions.
The Evaluate can only be called via name manager, see
evaluate function. Evalute evaluates a string as a formula, so anything that can be built as a string can be used as a formula.
Press Ctrl+F3, press New...
In the Name field, name your function (e.g. Repeater)
In the Reference field write your formula, using Rept:
=Evaluate(rept("sin(",b2) & a2 & rept(")",b2))and in your cell, you use =Repeater and specify number of repeats in B2 and the parameter in A2
Its a bit tricky, so a user defined formula in VBA might be easier
add a comment |
This might seem a bit crazy, but it's a hack that might help.
Suppose column A has all the text values and column B has the number of iterations you want. Also suppose that there is a max value for column B, like 4, for the sake of explaining. If you can't have a max value for column B, then this won't work.
First, add a row at the top that has the numbers 1 - 4 at the top of columns C, D, E, F. In cell C2, put a formula like this: =IF(C$1=$B2,DOSOMETHING(A2),"")
(for the last part of that formula, put ,"")
if you are working with strings, and ,0)
if you are working with numbers).
Then in D2, put =IF(C$1=$B2,DOSOMETHING(DOSOMETHING(A2)),"")
. And so on for E2 and F2. Now if you copy cells C2 - F2 and paste them down the C - F columns, you'll get this weird matrix where everything is blank (or zero) except for the columns where you have the value you want based on the number of iterations in column B.
Now for the last column (column G in my example), concatenate (or sum, if working with numbers) the four preceding columns to get the set of values you need all in one column. Optionally hide the four calculating columns.
Like this:
add a comment |
I think this is possible in the general case (with recursion)!
Essentially what we need to do is to set up a system where a counter cell (with a reference to itself) counts up to the number of function calls, and each time the number changes the function is run again.
Now to make this clearer I'm using table formulae. My table looks like this at the start:
The first step is to make tho Counter - the Current State
column contains this
=IFS(
resetSwitch,
[@[Counter Initial State]],
[@[Current State]] >= [@[Count To]],
[@[Current State]],
TRUE,
[@[Current State]] + 1
)
Which reads
If the resetSwitch is True, go to initial state, otherwise keep
increasing this cell's value until we reach the final value ([@[Count To]]
)
Now to determine whether to apply the formula (in the Recursor column) we need to know if the counter is increasing (Current State < Count To), if so we apply the formula. One way to do this is to get the counter's previous state, and if that's different from the current one, the counter is growing, if not the counter has reached its final value.
The Previous State
column contains
=IFS(
[@[Current State]] = [@[Counter Initial State]],
[@[Counter Initial State]],
[@[Current State]] < [@[Count To]],
[@[Current State]],
AND(
[@[Count To]] = [@[Current State]],
[@[Previous State]] < [@[Current State]] - 1
),
[@[Previous State]] + 1,
TRUE,
[@[Count To]]
)
Which reads:
If the counter is in the initial state, it must have been previously.
If the counter is currently less than its final value, its previous
state was one less than its current one. If the counter is at its
final state and the current value for previous state is not, then the
previous state should still be increasing. If the current value of
previous state is the end value then the counter is stable in its
final state
That takes a non-Excel mindset to get your head around I think, but I believe it works. Essentially Previous State
is one iteration behind Current State
(as the name would suggest)
Finally, we need to apply the formula whenever the previous state <> the current state of the counter (the counter is currently increasing). That gives the formula in Recursor
=IFS(
resetSwitch,
[@[Reursor Initial State]],
[@[Previous State]] < [@[Current State]],
[@Recursor] & "_checked",
TRUE,
[@Recursor]
)
Where here the formula to be applied n times is [@Recursor] & "_checked"
, but could be any f([@Recursor])
.
Setting resetSwitch
to FALSE makes everything run, resulting in
N.b this requires enabling iterative calculations, with number of iterations set to something greater than the max number times you want to apply the formula
– Greedo
Dec 12 at 21:15
add a comment |
To apply Formula in Cell `C1' n Numbers of times you need to apply Iteration.
How it works:
- Click File, Option then Formula.
- Find Enable Iterative Calculation Check box & just Check it.
- For Maximum Iterations write the value, for example 5.
- Write this formula in Cell
C1
=B1+C1
You find Excel calculates the Formula in C1
five times.
You can set New Value as many times you need, by following the Steps from 1 to 3.
1
That's not exactly what I'm looking for. I updated the question for clarification.
– Scripter22
Dec 12 at 13:14
@Scripter22, you have updated the question after been answered by me last evening,, and it's not a good practice. How much time people are investing here to post a good answer and you have make twist without considering it !!
– Rajesh S
Dec 13 at 6:27
@Scripter22, the show example to suffix string n number of times and you have added that the suggested method should applicable to any Formula!! I'm unable to understand what you are trying to achieve, better update the post properly,, otherwise it may attract to VOTE it to CLOSE!!
– Rajesh S
Dec 13 at 6:35
@Scripter22,, being a Excel user you are unable to understand that if without involving the Formula Cell, like CellC1
repeat any Formula n numbers of time will be useless. Like if you recycle Formula=SUM(A1:A5)
5 times is in CellA6
, every time it will return same value. That's was the reason I've shown to use ITERATION !!
– Rajesh S
Dec 13 at 6:39
add a comment |
6 Answers
6
active
oldest
votes
6 Answers
6
active
oldest
votes
active
oldest
votes
active
oldest
votes
Then I would use = Value & REPT("_checked", NoOfExecutions)
.
If you always need to do stuff like the one on the example (concatenating strings), it works pretty well.
If you need to use other formulas, this is what I can think of:
we separate the function you will need to use: beginning (everything that should go before the main argument) and end (anything that follows the argument, including additional arguments). For example if we use the function
LEFT(value, 2)
,LEFT(
will go in the Beginning,, 2)
in the End.we build the formula as text with concatenation and
REPT
. Referring to the example in the picture, formula in cell C6 will be:= "=" &REPT($B$2,B6) & $A6 & REPT($B$3,$B6)
Then you need to copy the cell and paste it as values in cell D6; then click on the formula in the formula bar and press Enter on your keyboard.
It's a few steps but it avoids VBA.
Pleas read the clarification I added to the end of the question. A general solution is required.
– robinCTS
Dec 12 at 13:32
Thanks for your help, robinCTS; I was just about to write something similar. What I am looking for is a general solution. But nice to have this very easy solution for string concatenation. Thanks, VFor.
– Scripter22
Dec 12 at 13:52
@Scripter22 You're welcome. FYI, you need to prepend an@
to a username in order for them to get pinged if they aren't the poster of the question/answer you are commenting on. See How do comment @replies work? for the full details.
– robinCTS
Dec 12 at 14:03
Nice try for a general solution! It doesn't quite work as the OP intends, though. The idea is to allow n to be changed and have the result automatically update. However, the main problem is that this only works for a limited number of formulas where "Value" only occurs once. (For example, the simple formula=LEFT(A1,LEN(A1)-1)
can't be expanded at all by usingREPT()
.)
– robinCTS
Dec 12 at 14:45
add a comment |
Then I would use = Value & REPT("_checked", NoOfExecutions)
.
If you always need to do stuff like the one on the example (concatenating strings), it works pretty well.
If you need to use other formulas, this is what I can think of:
we separate the function you will need to use: beginning (everything that should go before the main argument) and end (anything that follows the argument, including additional arguments). For example if we use the function
LEFT(value, 2)
,LEFT(
will go in the Beginning,, 2)
in the End.we build the formula as text with concatenation and
REPT
. Referring to the example in the picture, formula in cell C6 will be:= "=" &REPT($B$2,B6) & $A6 & REPT($B$3,$B6)
Then you need to copy the cell and paste it as values in cell D6; then click on the formula in the formula bar and press Enter on your keyboard.
It's a few steps but it avoids VBA.
Pleas read the clarification I added to the end of the question. A general solution is required.
– robinCTS
Dec 12 at 13:32
Thanks for your help, robinCTS; I was just about to write something similar. What I am looking for is a general solution. But nice to have this very easy solution for string concatenation. Thanks, VFor.
– Scripter22
Dec 12 at 13:52
@Scripter22 You're welcome. FYI, you need to prepend an@
to a username in order for them to get pinged if they aren't the poster of the question/answer you are commenting on. See How do comment @replies work? for the full details.
– robinCTS
Dec 12 at 14:03
Nice try for a general solution! It doesn't quite work as the OP intends, though. The idea is to allow n to be changed and have the result automatically update. However, the main problem is that this only works for a limited number of formulas where "Value" only occurs once. (For example, the simple formula=LEFT(A1,LEN(A1)-1)
can't be expanded at all by usingREPT()
.)
– robinCTS
Dec 12 at 14:45
add a comment |
Then I would use = Value & REPT("_checked", NoOfExecutions)
.
If you always need to do stuff like the one on the example (concatenating strings), it works pretty well.
If you need to use other formulas, this is what I can think of:
we separate the function you will need to use: beginning (everything that should go before the main argument) and end (anything that follows the argument, including additional arguments). For example if we use the function
LEFT(value, 2)
,LEFT(
will go in the Beginning,, 2)
in the End.we build the formula as text with concatenation and
REPT
. Referring to the example in the picture, formula in cell C6 will be:= "=" &REPT($B$2,B6) & $A6 & REPT($B$3,$B6)
Then you need to copy the cell and paste it as values in cell D6; then click on the formula in the formula bar and press Enter on your keyboard.
It's a few steps but it avoids VBA.
Then I would use = Value & REPT("_checked", NoOfExecutions)
.
If you always need to do stuff like the one on the example (concatenating strings), it works pretty well.
If you need to use other formulas, this is what I can think of:
we separate the function you will need to use: beginning (everything that should go before the main argument) and end (anything that follows the argument, including additional arguments). For example if we use the function
LEFT(value, 2)
,LEFT(
will go in the Beginning,, 2)
in the End.we build the formula as text with concatenation and
REPT
. Referring to the example in the picture, formula in cell C6 will be:= "=" &REPT($B$2,B6) & $A6 & REPT($B$3,$B6)
Then you need to copy the cell and paste it as values in cell D6; then click on the formula in the formula bar and press Enter on your keyboard.
It's a few steps but it avoids VBA.
edited Dec 12 at 14:06
robinCTS
3,99941527
3,99941527
answered Dec 12 at 13:28
VFor
516
516
Pleas read the clarification I added to the end of the question. A general solution is required.
– robinCTS
Dec 12 at 13:32
Thanks for your help, robinCTS; I was just about to write something similar. What I am looking for is a general solution. But nice to have this very easy solution for string concatenation. Thanks, VFor.
– Scripter22
Dec 12 at 13:52
@Scripter22 You're welcome. FYI, you need to prepend an@
to a username in order for them to get pinged if they aren't the poster of the question/answer you are commenting on. See How do comment @replies work? for the full details.
– robinCTS
Dec 12 at 14:03
Nice try for a general solution! It doesn't quite work as the OP intends, though. The idea is to allow n to be changed and have the result automatically update. However, the main problem is that this only works for a limited number of formulas where "Value" only occurs once. (For example, the simple formula=LEFT(A1,LEN(A1)-1)
can't be expanded at all by usingREPT()
.)
– robinCTS
Dec 12 at 14:45
add a comment |
Pleas read the clarification I added to the end of the question. A general solution is required.
– robinCTS
Dec 12 at 13:32
Thanks for your help, robinCTS; I was just about to write something similar. What I am looking for is a general solution. But nice to have this very easy solution for string concatenation. Thanks, VFor.
– Scripter22
Dec 12 at 13:52
@Scripter22 You're welcome. FYI, you need to prepend an@
to a username in order for them to get pinged if they aren't the poster of the question/answer you are commenting on. See How do comment @replies work? for the full details.
– robinCTS
Dec 12 at 14:03
Nice try for a general solution! It doesn't quite work as the OP intends, though. The idea is to allow n to be changed and have the result automatically update. However, the main problem is that this only works for a limited number of formulas where "Value" only occurs once. (For example, the simple formula=LEFT(A1,LEN(A1)-1)
can't be expanded at all by usingREPT()
.)
– robinCTS
Dec 12 at 14:45
Pleas read the clarification I added to the end of the question. A general solution is required.
– robinCTS
Dec 12 at 13:32
Pleas read the clarification I added to the end of the question. A general solution is required.
– robinCTS
Dec 12 at 13:32
Thanks for your help, robinCTS; I was just about to write something similar. What I am looking for is a general solution. But nice to have this very easy solution for string concatenation. Thanks, VFor.
– Scripter22
Dec 12 at 13:52
Thanks for your help, robinCTS; I was just about to write something similar. What I am looking for is a general solution. But nice to have this very easy solution for string concatenation. Thanks, VFor.
– Scripter22
Dec 12 at 13:52
@Scripter22 You're welcome. FYI, you need to prepend an
@
to a username in order for them to get pinged if they aren't the poster of the question/answer you are commenting on. See How do comment @replies work? for the full details.– robinCTS
Dec 12 at 14:03
@Scripter22 You're welcome. FYI, you need to prepend an
@
to a username in order for them to get pinged if they aren't the poster of the question/answer you are commenting on. See How do comment @replies work? for the full details.– robinCTS
Dec 12 at 14:03
Nice try for a general solution! It doesn't quite work as the OP intends, though. The idea is to allow n to be changed and have the result automatically update. However, the main problem is that this only works for a limited number of formulas where "Value" only occurs once. (For example, the simple formula
=LEFT(A1,LEN(A1)-1)
can't be expanded at all by using REPT()
.)– robinCTS
Dec 12 at 14:45
Nice try for a general solution! It doesn't quite work as the OP intends, though. The idea is to allow n to be changed and have the result automatically update. However, the main problem is that this only works for a limited number of formulas where "Value" only occurs once. (For example, the simple formula
=LEFT(A1,LEN(A1)-1)
can't be expanded at all by using REPT()
.)– robinCTS
Dec 12 at 14:45
add a comment |
No, sorry, this is not possible in the general case for any formula plus
having it update automatically. Without using VBA, that is.
However,it can be done for a very small number of specific formula (like concatenating a constant string). It can also be done, but with manual updating, for a certain set of formulas as cleverly shown in VFor's answer.
The closest you can get to a general solution is to rearrange the cells, embed the DOSOMETHING
formula in a special wrapper formula, and use helper columns.
For your supplied example worksheet:
Rearrange it like this:
Enter the following formula in D2
and ctrl-enter/copy-paste/fill-down&right/auto-fill into the rest of the table's columns:
=IF(COLUMN()-COLUMN($C2)>$A2,"§",C2&"_checked")
Enter the following formula in B2
and ctrl-enter/copy-paste/fill-down/auto-fill into the rest of the table's column:
=INDEX(C2:INDEX(2:2,1,COLUMNS(2:2)),MATCH("§",C2:INDEX(2:2,1,COLUMNS(2:2)),0)-1)
Note that the number of helper columns required is the maximum allowable value of n plus one. If there aren't enough of them for an entered value, an error ensues:
Explanation:
The generalised wrapper formula for the helper columns is:
=IF(COLUMN()-COLUMN($C2)>$A2,"§",DOSOMETHING(C2))
where DOSOMETHING(C2)
is any formula based on C2
only (for example, LEFT(C2,LEN(C2)-1)
which progressively removes the last character).
The wrapper formula works by operating on the cell to the left, thus effectively "nesting" the formulas the further to the right in the row it goes.
The IF(COLUMN()-COLUMN($C2)>$A2,"§",
part uses the column indexes to count down the number of times the DOSOMETHING
formula is nested, and once the number of times specified in column A
has been achieved it outputs terminator strings. These strings do not necessarily need to be §
. They just need to be something that will never be the result of the evaluation of any number of nested formulas for any allowable Value
.
The Result
formula looks trickier. However, the C2:INDEX(2:2, 1, COLUMNS(2:2))
parts are simply the sub-range of row 2
to the right of the Result
column.
The formula is thus essentially the same as:
=INDEX(2:2,MATCH("§",2:2,0)-1)
which makes it easier to understand.
(Note that this formula actually works if iterative calculations are enabled.)
Looking at this simpler formula, it is clear that the formula returns the n-level nested DOSOMETHING
function result.
I think I just wrote an answer similar to this, but slightly different. I think your answer is easier to copy paste across the matrix of helper columns if the number of possible iterations is very large. My answer might be easier to implement if the iteration maximum is fairly small. Brilliant answer!
– Todd Wilcox
Dec 12 at 19:43
add a comment |
No, sorry, this is not possible in the general case for any formula plus
having it update automatically. Without using VBA, that is.
However,it can be done for a very small number of specific formula (like concatenating a constant string). It can also be done, but with manual updating, for a certain set of formulas as cleverly shown in VFor's answer.
The closest you can get to a general solution is to rearrange the cells, embed the DOSOMETHING
formula in a special wrapper formula, and use helper columns.
For your supplied example worksheet:
Rearrange it like this:
Enter the following formula in D2
and ctrl-enter/copy-paste/fill-down&right/auto-fill into the rest of the table's columns:
=IF(COLUMN()-COLUMN($C2)>$A2,"§",C2&"_checked")
Enter the following formula in B2
and ctrl-enter/copy-paste/fill-down/auto-fill into the rest of the table's column:
=INDEX(C2:INDEX(2:2,1,COLUMNS(2:2)),MATCH("§",C2:INDEX(2:2,1,COLUMNS(2:2)),0)-1)
Note that the number of helper columns required is the maximum allowable value of n plus one. If there aren't enough of them for an entered value, an error ensues:
Explanation:
The generalised wrapper formula for the helper columns is:
=IF(COLUMN()-COLUMN($C2)>$A2,"§",DOSOMETHING(C2))
where DOSOMETHING(C2)
is any formula based on C2
only (for example, LEFT(C2,LEN(C2)-1)
which progressively removes the last character).
The wrapper formula works by operating on the cell to the left, thus effectively "nesting" the formulas the further to the right in the row it goes.
The IF(COLUMN()-COLUMN($C2)>$A2,"§",
part uses the column indexes to count down the number of times the DOSOMETHING
formula is nested, and once the number of times specified in column A
has been achieved it outputs terminator strings. These strings do not necessarily need to be §
. They just need to be something that will never be the result of the evaluation of any number of nested formulas for any allowable Value
.
The Result
formula looks trickier. However, the C2:INDEX(2:2, 1, COLUMNS(2:2))
parts are simply the sub-range of row 2
to the right of the Result
column.
The formula is thus essentially the same as:
=INDEX(2:2,MATCH("§",2:2,0)-1)
which makes it easier to understand.
(Note that this formula actually works if iterative calculations are enabled.)
Looking at this simpler formula, it is clear that the formula returns the n-level nested DOSOMETHING
function result.
I think I just wrote an answer similar to this, but slightly different. I think your answer is easier to copy paste across the matrix of helper columns if the number of possible iterations is very large. My answer might be easier to implement if the iteration maximum is fairly small. Brilliant answer!
– Todd Wilcox
Dec 12 at 19:43
add a comment |
No, sorry, this is not possible in the general case for any formula plus
having it update automatically. Without using VBA, that is.
However,it can be done for a very small number of specific formula (like concatenating a constant string). It can also be done, but with manual updating, for a certain set of formulas as cleverly shown in VFor's answer.
The closest you can get to a general solution is to rearrange the cells, embed the DOSOMETHING
formula in a special wrapper formula, and use helper columns.
For your supplied example worksheet:
Rearrange it like this:
Enter the following formula in D2
and ctrl-enter/copy-paste/fill-down&right/auto-fill into the rest of the table's columns:
=IF(COLUMN()-COLUMN($C2)>$A2,"§",C2&"_checked")
Enter the following formula in B2
and ctrl-enter/copy-paste/fill-down/auto-fill into the rest of the table's column:
=INDEX(C2:INDEX(2:2,1,COLUMNS(2:2)),MATCH("§",C2:INDEX(2:2,1,COLUMNS(2:2)),0)-1)
Note that the number of helper columns required is the maximum allowable value of n plus one. If there aren't enough of them for an entered value, an error ensues:
Explanation:
The generalised wrapper formula for the helper columns is:
=IF(COLUMN()-COLUMN($C2)>$A2,"§",DOSOMETHING(C2))
where DOSOMETHING(C2)
is any formula based on C2
only (for example, LEFT(C2,LEN(C2)-1)
which progressively removes the last character).
The wrapper formula works by operating on the cell to the left, thus effectively "nesting" the formulas the further to the right in the row it goes.
The IF(COLUMN()-COLUMN($C2)>$A2,"§",
part uses the column indexes to count down the number of times the DOSOMETHING
formula is nested, and once the number of times specified in column A
has been achieved it outputs terminator strings. These strings do not necessarily need to be §
. They just need to be something that will never be the result of the evaluation of any number of nested formulas for any allowable Value
.
The Result
formula looks trickier. However, the C2:INDEX(2:2, 1, COLUMNS(2:2))
parts are simply the sub-range of row 2
to the right of the Result
column.
The formula is thus essentially the same as:
=INDEX(2:2,MATCH("§",2:2,0)-1)
which makes it easier to understand.
(Note that this formula actually works if iterative calculations are enabled.)
Looking at this simpler formula, it is clear that the formula returns the n-level nested DOSOMETHING
function result.
No, sorry, this is not possible in the general case for any formula plus
having it update automatically. Without using VBA, that is.
However,it can be done for a very small number of specific formula (like concatenating a constant string). It can also be done, but with manual updating, for a certain set of formulas as cleverly shown in VFor's answer.
The closest you can get to a general solution is to rearrange the cells, embed the DOSOMETHING
formula in a special wrapper formula, and use helper columns.
For your supplied example worksheet:
Rearrange it like this:
Enter the following formula in D2
and ctrl-enter/copy-paste/fill-down&right/auto-fill into the rest of the table's columns:
=IF(COLUMN()-COLUMN($C2)>$A2,"§",C2&"_checked")
Enter the following formula in B2
and ctrl-enter/copy-paste/fill-down/auto-fill into the rest of the table's column:
=INDEX(C2:INDEX(2:2,1,COLUMNS(2:2)),MATCH("§",C2:INDEX(2:2,1,COLUMNS(2:2)),0)-1)
Note that the number of helper columns required is the maximum allowable value of n plus one. If there aren't enough of them for an entered value, an error ensues:
Explanation:
The generalised wrapper formula for the helper columns is:
=IF(COLUMN()-COLUMN($C2)>$A2,"§",DOSOMETHING(C2))
where DOSOMETHING(C2)
is any formula based on C2
only (for example, LEFT(C2,LEN(C2)-1)
which progressively removes the last character).
The wrapper formula works by operating on the cell to the left, thus effectively "nesting" the formulas the further to the right in the row it goes.
The IF(COLUMN()-COLUMN($C2)>$A2,"§",
part uses the column indexes to count down the number of times the DOSOMETHING
formula is nested, and once the number of times specified in column A
has been achieved it outputs terminator strings. These strings do not necessarily need to be §
. They just need to be something that will never be the result of the evaluation of any number of nested formulas for any allowable Value
.
The Result
formula looks trickier. However, the C2:INDEX(2:2, 1, COLUMNS(2:2))
parts are simply the sub-range of row 2
to the right of the Result
column.
The formula is thus essentially the same as:
=INDEX(2:2,MATCH("§",2:2,0)-1)
which makes it easier to understand.
(Note that this formula actually works if iterative calculations are enabled.)
Looking at this simpler formula, it is clear that the formula returns the n-level nested DOSOMETHING
function result.
edited Dec 12 at 18:07
answered Dec 12 at 13:57
robinCTS
3,99941527
3,99941527
I think I just wrote an answer similar to this, but slightly different. I think your answer is easier to copy paste across the matrix of helper columns if the number of possible iterations is very large. My answer might be easier to implement if the iteration maximum is fairly small. Brilliant answer!
– Todd Wilcox
Dec 12 at 19:43
add a comment |
I think I just wrote an answer similar to this, but slightly different. I think your answer is easier to copy paste across the matrix of helper columns if the number of possible iterations is very large. My answer might be easier to implement if the iteration maximum is fairly small. Brilliant answer!
– Todd Wilcox
Dec 12 at 19:43
I think I just wrote an answer similar to this, but slightly different. I think your answer is easier to copy paste across the matrix of helper columns if the number of possible iterations is very large. My answer might be easier to implement if the iteration maximum is fairly small. Brilliant answer!
– Todd Wilcox
Dec 12 at 19:43
I think I just wrote an answer similar to this, but slightly different. I think your answer is easier to copy paste across the matrix of helper columns if the number of possible iterations is very large. My answer might be easier to implement if the iteration maximum is fairly small. Brilliant answer!
– Todd Wilcox
Dec 12 at 19:43
add a comment |
It can be done via the Evaluate and Rept functions.
The Evaluate can only be called via name manager, see
evaluate function. Evalute evaluates a string as a formula, so anything that can be built as a string can be used as a formula.
Press Ctrl+F3, press New...
In the Name field, name your function (e.g. Repeater)
In the Reference field write your formula, using Rept:
=Evaluate(rept("sin(",b2) & a2 & rept(")",b2))and in your cell, you use =Repeater and specify number of repeats in B2 and the parameter in A2
Its a bit tricky, so a user defined formula in VBA might be easier
add a comment |
It can be done via the Evaluate and Rept functions.
The Evaluate can only be called via name manager, see
evaluate function. Evalute evaluates a string as a formula, so anything that can be built as a string can be used as a formula.
Press Ctrl+F3, press New...
In the Name field, name your function (e.g. Repeater)
In the Reference field write your formula, using Rept:
=Evaluate(rept("sin(",b2) & a2 & rept(")",b2))and in your cell, you use =Repeater and specify number of repeats in B2 and the parameter in A2
Its a bit tricky, so a user defined formula in VBA might be easier
add a comment |
It can be done via the Evaluate and Rept functions.
The Evaluate can only be called via name manager, see
evaluate function. Evalute evaluates a string as a formula, so anything that can be built as a string can be used as a formula.
Press Ctrl+F3, press New...
In the Name field, name your function (e.g. Repeater)
In the Reference field write your formula, using Rept:
=Evaluate(rept("sin(",b2) & a2 & rept(")",b2))and in your cell, you use =Repeater and specify number of repeats in B2 and the parameter in A2
Its a bit tricky, so a user defined formula in VBA might be easier
It can be done via the Evaluate and Rept functions.
The Evaluate can only be called via name manager, see
evaluate function. Evalute evaluates a string as a formula, so anything that can be built as a string can be used as a formula.
Press Ctrl+F3, press New...
In the Name field, name your function (e.g. Repeater)
In the Reference field write your formula, using Rept:
=Evaluate(rept("sin(",b2) & a2 & rept(")",b2))and in your cell, you use =Repeater and specify number of repeats in B2 and the parameter in A2
Its a bit tricky, so a user defined formula in VBA might be easier
answered Dec 12 at 18:53
Stefan
1011
1011
add a comment |
add a comment |
This might seem a bit crazy, but it's a hack that might help.
Suppose column A has all the text values and column B has the number of iterations you want. Also suppose that there is a max value for column B, like 4, for the sake of explaining. If you can't have a max value for column B, then this won't work.
First, add a row at the top that has the numbers 1 - 4 at the top of columns C, D, E, F. In cell C2, put a formula like this: =IF(C$1=$B2,DOSOMETHING(A2),"")
(for the last part of that formula, put ,"")
if you are working with strings, and ,0)
if you are working with numbers).
Then in D2, put =IF(C$1=$B2,DOSOMETHING(DOSOMETHING(A2)),"")
. And so on for E2 and F2. Now if you copy cells C2 - F2 and paste them down the C - F columns, you'll get this weird matrix where everything is blank (or zero) except for the columns where you have the value you want based on the number of iterations in column B.
Now for the last column (column G in my example), concatenate (or sum, if working with numbers) the four preceding columns to get the set of values you need all in one column. Optionally hide the four calculating columns.
Like this:
add a comment |
This might seem a bit crazy, but it's a hack that might help.
Suppose column A has all the text values and column B has the number of iterations you want. Also suppose that there is a max value for column B, like 4, for the sake of explaining. If you can't have a max value for column B, then this won't work.
First, add a row at the top that has the numbers 1 - 4 at the top of columns C, D, E, F. In cell C2, put a formula like this: =IF(C$1=$B2,DOSOMETHING(A2),"")
(for the last part of that formula, put ,"")
if you are working with strings, and ,0)
if you are working with numbers).
Then in D2, put =IF(C$1=$B2,DOSOMETHING(DOSOMETHING(A2)),"")
. And so on for E2 and F2. Now if you copy cells C2 - F2 and paste them down the C - F columns, you'll get this weird matrix where everything is blank (or zero) except for the columns where you have the value you want based on the number of iterations in column B.
Now for the last column (column G in my example), concatenate (or sum, if working with numbers) the four preceding columns to get the set of values you need all in one column. Optionally hide the four calculating columns.
Like this:
add a comment |
This might seem a bit crazy, but it's a hack that might help.
Suppose column A has all the text values and column B has the number of iterations you want. Also suppose that there is a max value for column B, like 4, for the sake of explaining. If you can't have a max value for column B, then this won't work.
First, add a row at the top that has the numbers 1 - 4 at the top of columns C, D, E, F. In cell C2, put a formula like this: =IF(C$1=$B2,DOSOMETHING(A2),"")
(for the last part of that formula, put ,"")
if you are working with strings, and ,0)
if you are working with numbers).
Then in D2, put =IF(C$1=$B2,DOSOMETHING(DOSOMETHING(A2)),"")
. And so on for E2 and F2. Now if you copy cells C2 - F2 and paste them down the C - F columns, you'll get this weird matrix where everything is blank (or zero) except for the columns where you have the value you want based on the number of iterations in column B.
Now for the last column (column G in my example), concatenate (or sum, if working with numbers) the four preceding columns to get the set of values you need all in one column. Optionally hide the four calculating columns.
Like this:
This might seem a bit crazy, but it's a hack that might help.
Suppose column A has all the text values and column B has the number of iterations you want. Also suppose that there is a max value for column B, like 4, for the sake of explaining. If you can't have a max value for column B, then this won't work.
First, add a row at the top that has the numbers 1 - 4 at the top of columns C, D, E, F. In cell C2, put a formula like this: =IF(C$1=$B2,DOSOMETHING(A2),"")
(for the last part of that formula, put ,"")
if you are working with strings, and ,0)
if you are working with numbers).
Then in D2, put =IF(C$1=$B2,DOSOMETHING(DOSOMETHING(A2)),"")
. And so on for E2 and F2. Now if you copy cells C2 - F2 and paste them down the C - F columns, you'll get this weird matrix where everything is blank (or zero) except for the columns where you have the value you want based on the number of iterations in column B.
Now for the last column (column G in my example), concatenate (or sum, if working with numbers) the four preceding columns to get the set of values you need all in one column. Optionally hide the four calculating columns.
Like this:
answered Dec 12 at 19:37
Todd Wilcox
4661413
4661413
add a comment |
add a comment |
I think this is possible in the general case (with recursion)!
Essentially what we need to do is to set up a system where a counter cell (with a reference to itself) counts up to the number of function calls, and each time the number changes the function is run again.
Now to make this clearer I'm using table formulae. My table looks like this at the start:
The first step is to make tho Counter - the Current State
column contains this
=IFS(
resetSwitch,
[@[Counter Initial State]],
[@[Current State]] >= [@[Count To]],
[@[Current State]],
TRUE,
[@[Current State]] + 1
)
Which reads
If the resetSwitch is True, go to initial state, otherwise keep
increasing this cell's value until we reach the final value ([@[Count To]]
)
Now to determine whether to apply the formula (in the Recursor column) we need to know if the counter is increasing (Current State < Count To), if so we apply the formula. One way to do this is to get the counter's previous state, and if that's different from the current one, the counter is growing, if not the counter has reached its final value.
The Previous State
column contains
=IFS(
[@[Current State]] = [@[Counter Initial State]],
[@[Counter Initial State]],
[@[Current State]] < [@[Count To]],
[@[Current State]],
AND(
[@[Count To]] = [@[Current State]],
[@[Previous State]] < [@[Current State]] - 1
),
[@[Previous State]] + 1,
TRUE,
[@[Count To]]
)
Which reads:
If the counter is in the initial state, it must have been previously.
If the counter is currently less than its final value, its previous
state was one less than its current one. If the counter is at its
final state and the current value for previous state is not, then the
previous state should still be increasing. If the current value of
previous state is the end value then the counter is stable in its
final state
That takes a non-Excel mindset to get your head around I think, but I believe it works. Essentially Previous State
is one iteration behind Current State
(as the name would suggest)
Finally, we need to apply the formula whenever the previous state <> the current state of the counter (the counter is currently increasing). That gives the formula in Recursor
=IFS(
resetSwitch,
[@[Reursor Initial State]],
[@[Previous State]] < [@[Current State]],
[@Recursor] & "_checked",
TRUE,
[@Recursor]
)
Where here the formula to be applied n times is [@Recursor] & "_checked"
, but could be any f([@Recursor])
.
Setting resetSwitch
to FALSE makes everything run, resulting in
N.b this requires enabling iterative calculations, with number of iterations set to something greater than the max number times you want to apply the formula
– Greedo
Dec 12 at 21:15
add a comment |
I think this is possible in the general case (with recursion)!
Essentially what we need to do is to set up a system where a counter cell (with a reference to itself) counts up to the number of function calls, and each time the number changes the function is run again.
Now to make this clearer I'm using table formulae. My table looks like this at the start:
The first step is to make tho Counter - the Current State
column contains this
=IFS(
resetSwitch,
[@[Counter Initial State]],
[@[Current State]] >= [@[Count To]],
[@[Current State]],
TRUE,
[@[Current State]] + 1
)
Which reads
If the resetSwitch is True, go to initial state, otherwise keep
increasing this cell's value until we reach the final value ([@[Count To]]
)
Now to determine whether to apply the formula (in the Recursor column) we need to know if the counter is increasing (Current State < Count To), if so we apply the formula. One way to do this is to get the counter's previous state, and if that's different from the current one, the counter is growing, if not the counter has reached its final value.
The Previous State
column contains
=IFS(
[@[Current State]] = [@[Counter Initial State]],
[@[Counter Initial State]],
[@[Current State]] < [@[Count To]],
[@[Current State]],
AND(
[@[Count To]] = [@[Current State]],
[@[Previous State]] < [@[Current State]] - 1
),
[@[Previous State]] + 1,
TRUE,
[@[Count To]]
)
Which reads:
If the counter is in the initial state, it must have been previously.
If the counter is currently less than its final value, its previous
state was one less than its current one. If the counter is at its
final state and the current value for previous state is not, then the
previous state should still be increasing. If the current value of
previous state is the end value then the counter is stable in its
final state
That takes a non-Excel mindset to get your head around I think, but I believe it works. Essentially Previous State
is one iteration behind Current State
(as the name would suggest)
Finally, we need to apply the formula whenever the previous state <> the current state of the counter (the counter is currently increasing). That gives the formula in Recursor
=IFS(
resetSwitch,
[@[Reursor Initial State]],
[@[Previous State]] < [@[Current State]],
[@Recursor] & "_checked",
TRUE,
[@Recursor]
)
Where here the formula to be applied n times is [@Recursor] & "_checked"
, but could be any f([@Recursor])
.
Setting resetSwitch
to FALSE makes everything run, resulting in
N.b this requires enabling iterative calculations, with number of iterations set to something greater than the max number times you want to apply the formula
– Greedo
Dec 12 at 21:15
add a comment |
I think this is possible in the general case (with recursion)!
Essentially what we need to do is to set up a system where a counter cell (with a reference to itself) counts up to the number of function calls, and each time the number changes the function is run again.
Now to make this clearer I'm using table formulae. My table looks like this at the start:
The first step is to make tho Counter - the Current State
column contains this
=IFS(
resetSwitch,
[@[Counter Initial State]],
[@[Current State]] >= [@[Count To]],
[@[Current State]],
TRUE,
[@[Current State]] + 1
)
Which reads
If the resetSwitch is True, go to initial state, otherwise keep
increasing this cell's value until we reach the final value ([@[Count To]]
)
Now to determine whether to apply the formula (in the Recursor column) we need to know if the counter is increasing (Current State < Count To), if so we apply the formula. One way to do this is to get the counter's previous state, and if that's different from the current one, the counter is growing, if not the counter has reached its final value.
The Previous State
column contains
=IFS(
[@[Current State]] = [@[Counter Initial State]],
[@[Counter Initial State]],
[@[Current State]] < [@[Count To]],
[@[Current State]],
AND(
[@[Count To]] = [@[Current State]],
[@[Previous State]] < [@[Current State]] - 1
),
[@[Previous State]] + 1,
TRUE,
[@[Count To]]
)
Which reads:
If the counter is in the initial state, it must have been previously.
If the counter is currently less than its final value, its previous
state was one less than its current one. If the counter is at its
final state and the current value for previous state is not, then the
previous state should still be increasing. If the current value of
previous state is the end value then the counter is stable in its
final state
That takes a non-Excel mindset to get your head around I think, but I believe it works. Essentially Previous State
is one iteration behind Current State
(as the name would suggest)
Finally, we need to apply the formula whenever the previous state <> the current state of the counter (the counter is currently increasing). That gives the formula in Recursor
=IFS(
resetSwitch,
[@[Reursor Initial State]],
[@[Previous State]] < [@[Current State]],
[@Recursor] & "_checked",
TRUE,
[@Recursor]
)
Where here the formula to be applied n times is [@Recursor] & "_checked"
, but could be any f([@Recursor])
.
Setting resetSwitch
to FALSE makes everything run, resulting in
I think this is possible in the general case (with recursion)!
Essentially what we need to do is to set up a system where a counter cell (with a reference to itself) counts up to the number of function calls, and each time the number changes the function is run again.
Now to make this clearer I'm using table formulae. My table looks like this at the start:
The first step is to make tho Counter - the Current State
column contains this
=IFS(
resetSwitch,
[@[Counter Initial State]],
[@[Current State]] >= [@[Count To]],
[@[Current State]],
TRUE,
[@[Current State]] + 1
)
Which reads
If the resetSwitch is True, go to initial state, otherwise keep
increasing this cell's value until we reach the final value ([@[Count To]]
)
Now to determine whether to apply the formula (in the Recursor column) we need to know if the counter is increasing (Current State < Count To), if so we apply the formula. One way to do this is to get the counter's previous state, and if that's different from the current one, the counter is growing, if not the counter has reached its final value.
The Previous State
column contains
=IFS(
[@[Current State]] = [@[Counter Initial State]],
[@[Counter Initial State]],
[@[Current State]] < [@[Count To]],
[@[Current State]],
AND(
[@[Count To]] = [@[Current State]],
[@[Previous State]] < [@[Current State]] - 1
),
[@[Previous State]] + 1,
TRUE,
[@[Count To]]
)
Which reads:
If the counter is in the initial state, it must have been previously.
If the counter is currently less than its final value, its previous
state was one less than its current one. If the counter is at its
final state and the current value for previous state is not, then the
previous state should still be increasing. If the current value of
previous state is the end value then the counter is stable in its
final state
That takes a non-Excel mindset to get your head around I think, but I believe it works. Essentially Previous State
is one iteration behind Current State
(as the name would suggest)
Finally, we need to apply the formula whenever the previous state <> the current state of the counter (the counter is currently increasing). That gives the formula in Recursor
=IFS(
resetSwitch,
[@[Reursor Initial State]],
[@[Previous State]] < [@[Current State]],
[@Recursor] & "_checked",
TRUE,
[@Recursor]
)
Where here the formula to be applied n times is [@Recursor] & "_checked"
, but could be any f([@Recursor])
.
Setting resetSwitch
to FALSE makes everything run, resulting in
edited Dec 12 at 22:12
answered Dec 12 at 21:08
Greedo
235415
235415
N.b this requires enabling iterative calculations, with number of iterations set to something greater than the max number times you want to apply the formula
– Greedo
Dec 12 at 21:15
add a comment |
N.b this requires enabling iterative calculations, with number of iterations set to something greater than the max number times you want to apply the formula
– Greedo
Dec 12 at 21:15
N.b this requires enabling iterative calculations, with number of iterations set to something greater than the max number times you want to apply the formula
– Greedo
Dec 12 at 21:15
N.b this requires enabling iterative calculations, with number of iterations set to something greater than the max number times you want to apply the formula
– Greedo
Dec 12 at 21:15
add a comment |
To apply Formula in Cell `C1' n Numbers of times you need to apply Iteration.
How it works:
- Click File, Option then Formula.
- Find Enable Iterative Calculation Check box & just Check it.
- For Maximum Iterations write the value, for example 5.
- Write this formula in Cell
C1
=B1+C1
You find Excel calculates the Formula in C1
five times.
You can set New Value as many times you need, by following the Steps from 1 to 3.
1
That's not exactly what I'm looking for. I updated the question for clarification.
– Scripter22
Dec 12 at 13:14
@Scripter22, you have updated the question after been answered by me last evening,, and it's not a good practice. How much time people are investing here to post a good answer and you have make twist without considering it !!
– Rajesh S
Dec 13 at 6:27
@Scripter22, the show example to suffix string n number of times and you have added that the suggested method should applicable to any Formula!! I'm unable to understand what you are trying to achieve, better update the post properly,, otherwise it may attract to VOTE it to CLOSE!!
– Rajesh S
Dec 13 at 6:35
@Scripter22,, being a Excel user you are unable to understand that if without involving the Formula Cell, like CellC1
repeat any Formula n numbers of time will be useless. Like if you recycle Formula=SUM(A1:A5)
5 times is in CellA6
, every time it will return same value. That's was the reason I've shown to use ITERATION !!
– Rajesh S
Dec 13 at 6:39
add a comment |
To apply Formula in Cell `C1' n Numbers of times you need to apply Iteration.
How it works:
- Click File, Option then Formula.
- Find Enable Iterative Calculation Check box & just Check it.
- For Maximum Iterations write the value, for example 5.
- Write this formula in Cell
C1
=B1+C1
You find Excel calculates the Formula in C1
five times.
You can set New Value as many times you need, by following the Steps from 1 to 3.
1
That's not exactly what I'm looking for. I updated the question for clarification.
– Scripter22
Dec 12 at 13:14
@Scripter22, you have updated the question after been answered by me last evening,, and it's not a good practice. How much time people are investing here to post a good answer and you have make twist without considering it !!
– Rajesh S
Dec 13 at 6:27
@Scripter22, the show example to suffix string n number of times and you have added that the suggested method should applicable to any Formula!! I'm unable to understand what you are trying to achieve, better update the post properly,, otherwise it may attract to VOTE it to CLOSE!!
– Rajesh S
Dec 13 at 6:35
@Scripter22,, being a Excel user you are unable to understand that if without involving the Formula Cell, like CellC1
repeat any Formula n numbers of time will be useless. Like if you recycle Formula=SUM(A1:A5)
5 times is in CellA6
, every time it will return same value. That's was the reason I've shown to use ITERATION !!
– Rajesh S
Dec 13 at 6:39
add a comment |
To apply Formula in Cell `C1' n Numbers of times you need to apply Iteration.
How it works:
- Click File, Option then Formula.
- Find Enable Iterative Calculation Check box & just Check it.
- For Maximum Iterations write the value, for example 5.
- Write this formula in Cell
C1
=B1+C1
You find Excel calculates the Formula in C1
five times.
You can set New Value as many times you need, by following the Steps from 1 to 3.
To apply Formula in Cell `C1' n Numbers of times you need to apply Iteration.
How it works:
- Click File, Option then Formula.
- Find Enable Iterative Calculation Check box & just Check it.
- For Maximum Iterations write the value, for example 5.
- Write this formula in Cell
C1
=B1+C1
You find Excel calculates the Formula in C1
five times.
You can set New Value as many times you need, by following the Steps from 1 to 3.
edited Dec 12 at 12:13
answered Dec 12 at 12:05
Rajesh S
3,6481522
3,6481522
1
That's not exactly what I'm looking for. I updated the question for clarification.
– Scripter22
Dec 12 at 13:14
@Scripter22, you have updated the question after been answered by me last evening,, and it's not a good practice. How much time people are investing here to post a good answer and you have make twist without considering it !!
– Rajesh S
Dec 13 at 6:27
@Scripter22, the show example to suffix string n number of times and you have added that the suggested method should applicable to any Formula!! I'm unable to understand what you are trying to achieve, better update the post properly,, otherwise it may attract to VOTE it to CLOSE!!
– Rajesh S
Dec 13 at 6:35
@Scripter22,, being a Excel user you are unable to understand that if without involving the Formula Cell, like CellC1
repeat any Formula n numbers of time will be useless. Like if you recycle Formula=SUM(A1:A5)
5 times is in CellA6
, every time it will return same value. That's was the reason I've shown to use ITERATION !!
– Rajesh S
Dec 13 at 6:39
add a comment |
1
That's not exactly what I'm looking for. I updated the question for clarification.
– Scripter22
Dec 12 at 13:14
@Scripter22, you have updated the question after been answered by me last evening,, and it's not a good practice. How much time people are investing here to post a good answer and you have make twist without considering it !!
– Rajesh S
Dec 13 at 6:27
@Scripter22, the show example to suffix string n number of times and you have added that the suggested method should applicable to any Formula!! I'm unable to understand what you are trying to achieve, better update the post properly,, otherwise it may attract to VOTE it to CLOSE!!
– Rajesh S
Dec 13 at 6:35
@Scripter22,, being a Excel user you are unable to understand that if without involving the Formula Cell, like CellC1
repeat any Formula n numbers of time will be useless. Like if you recycle Formula=SUM(A1:A5)
5 times is in CellA6
, every time it will return same value. That's was the reason I've shown to use ITERATION !!
– Rajesh S
Dec 13 at 6:39
1
1
That's not exactly what I'm looking for. I updated the question for clarification.
– Scripter22
Dec 12 at 13:14
That's not exactly what I'm looking for. I updated the question for clarification.
– Scripter22
Dec 12 at 13:14
@Scripter22, you have updated the question after been answered by me last evening,, and it's not a good practice. How much time people are investing here to post a good answer and you have make twist without considering it !!
– Rajesh S
Dec 13 at 6:27
@Scripter22, you have updated the question after been answered by me last evening,, and it's not a good practice. How much time people are investing here to post a good answer and you have make twist without considering it !!
– Rajesh S
Dec 13 at 6:27
@Scripter22, the show example to suffix string n number of times and you have added that the suggested method should applicable to any Formula!! I'm unable to understand what you are trying to achieve, better update the post properly,, otherwise it may attract to VOTE it to CLOSE!!
– Rajesh S
Dec 13 at 6:35
@Scripter22, the show example to suffix string n number of times and you have added that the suggested method should applicable to any Formula!! I'm unable to understand what you are trying to achieve, better update the post properly,, otherwise it may attract to VOTE it to CLOSE!!
– Rajesh S
Dec 13 at 6:35
@Scripter22,, being a Excel user you are unable to understand that if without involving the Formula Cell, like Cell
C1
repeat any Formula n numbers of time will be useless. Like if you recycle Formula =SUM(A1:A5)
5 times is in Cell A6
, every time it will return same value. That's was the reason I've shown to use ITERATION !!– Rajesh S
Dec 13 at 6:39
@Scripter22,, being a Excel user you are unable to understand that if without involving the Formula Cell, like Cell
C1
repeat any Formula n numbers of time will be useless. Like if you recycle Formula =SUM(A1:A5)
5 times is in Cell A6
, every time it will return same value. That's was the reason I've shown to use ITERATION !!– Rajesh S
Dec 13 at 6:39
add a comment |
3
Do you have a specific formula in mind you're trying to run multiple times? This sounds a lot like a potential XY Problem. Can you briefly describe what you're trying to accomplish/*why* to run the formula three times? In a way, what you used as an example (
=value&"_checked"
) would have a different way than if you wanted to run anIndex/Match
three times or so...– BruceWayne
Dec 12 at 16:41