QUESTION :
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.
ANSWER :
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.
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.
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.
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