Apply any formula n times without using VBA?











up vote
7
down vote

favorite












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.










share|improve this question









New contributor




Scripter22 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
















  • 1




    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
    13 hours ago

















up vote
7
down vote

favorite












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.










share|improve this question









New contributor




Scripter22 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
















  • 1




    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
    13 hours ago















up vote
7
down vote

favorite









up vote
7
down vote

favorite











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.










share|improve this question









New contributor




Scripter22 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











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.







microsoft-excel worksheet-function






share|improve this question









New contributor




Scripter22 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











share|improve this question









New contributor




Scripter22 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









share|improve this question




share|improve this question








edited 16 hours ago









robinCTS

3,94441527




3,94441527






New contributor




Scripter22 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









asked 18 hours ago









Scripter22

362




362




New contributor




Scripter22 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





New contributor





Scripter22 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.






Scripter22 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.








  • 1




    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
    13 hours ago
















  • 1




    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
    13 hours ago










1




1




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
13 hours ago






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
13 hours ago












6 Answers
6






active

oldest

votes

















up vote
5
down vote













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






share|improve this answer










New contributor




VFor is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.


















  • Pleas read the clarification I added to the end of the question. A general solution is required.
    – robinCTS
    16 hours ago












  • 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
    16 hours ago










  • @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
    16 hours ago










  • 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
    15 hours ago


















up vote
3
down vote













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.






share|improve this answer























  • 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
    10 hours ago


















up vote
0
down vote













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.






share|improve this answer



















  • 1




    That's not exactly what I'm looking for. I updated the question for clarification.
    – Scripter22
    17 hours ago


















up vote
0
down vote













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






share|improve this answer




























    up vote
    0
    down vote













    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






    share|improve this answer




























      up vote
      0
      down vote













      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






      share|improve this answer























      • 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
        9 hours ago











      Your Answer








      StackExchange.ready(function() {
      var channelOptions = {
      tags: "".split(" "),
      id: "3"
      };
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function() {
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled) {
      StackExchange.using("snippets", function() {
      createEditor();
      });
      }
      else {
      createEditor();
      }
      });

      function createEditor() {
      StackExchange.prepareEditor({
      heartbeatType: 'answer',
      convertImagesToLinks: true,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: 10,
      bindNavPrevention: true,
      postfix: "",
      imageUploader: {
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      },
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      });


      }
      });






      Scripter22 is a new contributor. Be nice, and check out our Code of Conduct.










      draft saved

      draft discarded


















      StackExchange.ready(
      function () {
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1382937%2fapply-any-formula-n-times-without-using-vba%23new-answer', 'question_page');
      }
      );

      Post as a guest















      Required, but never shown

























      6 Answers
      6






      active

      oldest

      votes








      6 Answers
      6






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes








      up vote
      5
      down vote













      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






      share|improve this answer










      New contributor




      VFor is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.


















      • Pleas read the clarification I added to the end of the question. A general solution is required.
        – robinCTS
        16 hours ago












      • 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
        16 hours ago










      • @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
        16 hours ago










      • 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
        15 hours ago















      up vote
      5
      down vote













      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






      share|improve this answer










      New contributor




      VFor is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.


















      • Pleas read the clarification I added to the end of the question. A general solution is required.
        – robinCTS
        16 hours ago












      • 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
        16 hours ago










      • @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
        16 hours ago










      • 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
        15 hours ago













      up vote
      5
      down vote










      up vote
      5
      down vote









      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






      share|improve this answer










      New contributor




      VFor is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      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







      share|improve this answer










      New contributor




      VFor is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      share|improve this answer



      share|improve this answer








      edited 16 hours ago









      robinCTS

      3,94441527




      3,94441527






      New contributor




      VFor is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      answered 16 hours ago









      VFor

      515




      515




      New contributor




      VFor is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.





      New contributor





      VFor is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






      VFor is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.












      • Pleas read the clarification I added to the end of the question. A general solution is required.
        – robinCTS
        16 hours ago












      • 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
        16 hours ago










      • @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
        16 hours ago










      • 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
        15 hours ago


















      • Pleas read the clarification I added to the end of the question. A general solution is required.
        – robinCTS
        16 hours ago












      • 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
        16 hours ago










      • @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
        16 hours ago










      • 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
        15 hours ago
















      Pleas read the clarification I added to the end of the question. A general solution is required.
      – robinCTS
      16 hours ago






      Pleas read the clarification I added to the end of the question. A general solution is required.
      – robinCTS
      16 hours ago














      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
      16 hours ago




      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
      16 hours ago












      @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
      16 hours ago




      @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
      16 hours ago












      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
      15 hours ago




      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
      15 hours ago












      up vote
      3
      down vote













      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.






      share|improve this answer























      • 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
        10 hours ago















      up vote
      3
      down vote













      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.






      share|improve this answer























      • 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
        10 hours ago













      up vote
      3
      down vote










      up vote
      3
      down vote









      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.






      share|improve this answer














      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.







      share|improve this answer














      share|improve this answer



      share|improve this answer








      edited 12 hours ago

























      answered 16 hours ago









      robinCTS

      3,94441527




      3,94441527












      • 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
        10 hours ago


















      • 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
        10 hours ago
















      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
      10 hours ago




      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
      10 hours ago










      up vote
      0
      down vote













      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.






      share|improve this answer



















      • 1




        That's not exactly what I'm looking for. I updated the question for clarification.
        – Scripter22
        17 hours ago















      up vote
      0
      down vote













      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.






      share|improve this answer



















      • 1




        That's not exactly what I'm looking for. I updated the question for clarification.
        – Scripter22
        17 hours ago













      up vote
      0
      down vote










      up vote
      0
      down vote









      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.






      share|improve this answer














      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.







      share|improve this answer














      share|improve this answer



      share|improve this answer








      edited 18 hours ago

























      answered 18 hours ago









      Rajesh S

      3,5481522




      3,5481522








      • 1




        That's not exactly what I'm looking for. I updated the question for clarification.
        – Scripter22
        17 hours ago














      • 1




        That's not exactly what I'm looking for. I updated the question for clarification.
        – Scripter22
        17 hours ago








      1




      1




      That's not exactly what I'm looking for. I updated the question for clarification.
      – Scripter22
      17 hours ago




      That's not exactly what I'm looking for. I updated the question for clarification.
      – Scripter22
      17 hours ago










      up vote
      0
      down vote













      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






      share|improve this answer

























        up vote
        0
        down vote













        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






        share|improve this answer























          up vote
          0
          down vote










          up vote
          0
          down vote









          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






          share|improve this answer












          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







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered 11 hours ago









          Stefan

          1011




          1011






















              up vote
              0
              down vote













              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






              share|improve this answer

























                up vote
                0
                down vote













                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






                share|improve this answer























                  up vote
                  0
                  down vote










                  up vote
                  0
                  down vote









                  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






                  share|improve this answer












                  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







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered 10 hours ago









                  Todd Wilcox

                  4661413




                  4661413






















                      up vote
                      0
                      down vote













                      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






                      share|improve this answer























                      • 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
                        9 hours ago















                      up vote
                      0
                      down vote













                      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






                      share|improve this answer























                      • 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
                        9 hours ago













                      up vote
                      0
                      down vote










                      up vote
                      0
                      down vote









                      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






                      share|improve this answer














                      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







                      share|improve this answer














                      share|improve this answer



                      share|improve this answer








                      edited 8 hours ago

























                      answered 9 hours ago









                      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
                        9 hours ago


















                      • 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
                        9 hours ago
















                      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
                      9 hours ago




                      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
                      9 hours ago










                      Scripter22 is a new contributor. Be nice, and check out our Code of Conduct.










                      draft saved

                      draft discarded


















                      Scripter22 is a new contributor. Be nice, and check out our Code of Conduct.













                      Scripter22 is a new contributor. Be nice, and check out our Code of Conduct.












                      Scripter22 is a new contributor. Be nice, and check out our Code of Conduct.
















                      Thanks for contributing an answer to Super User!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.





                      Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


                      Please pay close attention to the following guidance:


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function () {
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1382937%2fapply-any-formula-n-times-without-using-vba%23new-answer', 'question_page');
                      }
                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      Popular posts from this blog

                      Ellipse (mathématiques)

                      Quarter-circle Tiles

                      Mont Emei