7

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:

Screenshot of example worksheet

Please note that the solution should work for any formula, and not just for appending a constant string like in the example.

closed as too broad by Rajesh S, bertieb, fixer1234, agtoever, G-Man Dec 15 '18 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 an Index/Match three times or so... – BruceWayne Dec 12 '18 at 16:41
5

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.

Example Picture

  • Pleas read the clarification I added to the end of the question. A general solution is required. – robinCTS Dec 12 '18 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 '18 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 '18 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 '18 at 14:45
4

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:

Worksheet screenshot showing OP example

Rearrange it like this:

Worksheet screenshot showing rearrangement

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:

Worksheet screenshot showing error

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 '18 at 19:43
0

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

0

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:

enter image description here

0

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:

enter image description here

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

After clicking

  • 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 '18 at 21:15
-1

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

enter image description here

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.

  • 1
    That's not exactly what I'm looking for. I updated the question for clarification. – Scripter22 Dec 12 '18 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 '18 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 '18 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 '18 at 6:39

Not the answer you're looking for? Browse other questions tagged or ask your own question.