Apply any formula n times without using VBA?

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 1

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.

Answer 2

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.

Answer 3

To apply Formula in Cell `C1' n Numbers of times you need to apply Iteration.

How it works:

1. Click File, Option then Formula.
2. Find Enable Iterative Calculation Check box & just Check it.
3. For Maximum Iterations write the value, for example 5.
4. 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.

Answer 4

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

Answer 5

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:

Answer 6

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