How to loop in excel without VBA or macros

Question

Is it possible to iterate  loop  a group of rows in Excel without VBA or macros installed  A web search didn t yield anything helpful    IF  testsheet   C 1  lt   99  testsheet   A 1      amp  IF  testsheet   C 2  lt   99   testsheet   A 2      amp  IF  testsheet   C 3  lt   99   testsheet   A 3      amp      and so on through   C 40    A 40       As it is  I ll have to repeat the above code 40 times in each cell and I have over 200 cells which need the code   I m pretty good with PHP SQL  but just learning Excel

User · Answer

Add more columns when you have variable loops that repeat at different rates. I'm not sure explicitly what you're trying to do, but I think I've done something that could apply.

Creating a single loop in Excel is prettty simple. It actually does the work for you. Try this on a new workbook

Enter "1" in A1
Enter "=A1+1" in A2

A3 will automatically be "=A2+1" as you drag down. The first steps don't have to be that explicit. Excel will automatically recognize the pattern and count if you just put "2" in A2, but if we want B1-B5 to be "100" and B5-B10 to be "200" (counting up the same way) you can see why knowing how to do it explicitly matters. In this scenario, You just enter:

"100" in B1, drag through to B5 and
"=B1+100" in B6

B7 will automatically be "=B2+100" etc. as you drag down, so basically it increases every 5 rows infinitely. To make a loop of numbers 1-5 in column A:

Enter "=A1" in cell A6. As you drag down, it will automatically be "=A2" in cell A7, etc. because of the way that Excel does things.

So, now we have column A repeating numbers 1-5 while column B is increasing by 100 every 5 cells.You could make column B repeat, for instance, the numbers 100-900 in using the same method as you did with column A as a way to produce, for instance, each possible combination with multiple variables. Drag down the columns and they'll do it infinitely. I'm not explicitly addressing the scenario given, but if you follow the steps and understand them, the concept should give you an answer to the problem that involves adding more columns and concactinating or using them as your variables.

User · Answer

You could create a table somewhere on a calculation spreadsheet which performs this operation for each pair of cells, and use auto-fill to fill it up.

Aggregate the results from that table into a results cell.

The 200 so cells which reference the results could then reference the cell that holds the aggregation results. In the newest versions of excel you can name the result cell and reference it that way, for ease of reading.

User · Answer

I was just searching for something similar:

I want to sum every odd row column.

SUMIF has TWO possible ranges, the range to sum from, and a range to consider criteria in.

SUMIF(B1:B1000,1,A1:A1000)

This function will consider if a cell in the B range is "=1", it will sum the corresponding A cell only if it is.

To get "=1" to return in the B range I put this in B:

=MOD(ROWNUM(B1),2)

Then auto fill down to get the modulus to fill, you could put and calculatable criteria here to get the SUMIF or SUMIFS conditions you need to loop through each cell.

Easier than ARRAY stuff and hides the back-end of loops!

User · Answer

Nat gave a good answer  But since there is no way to shorten a code  why not use contatenate to  generate  the code you need  It works for me when I m lazy  at typing the whole code in the cell    So what we need is just identify the pattern   use excel to built the pattern  structure    add       and paste it in the intended cell    For example  you want to achieve  i mean  enter in the cell      IF  testsheet   C 1  lt   99  testsheet   A 1      amp IF  testsheet   C 2  lt   99  testsheet   A 2      amp IF  testsheet   C 3  lt   99  testsheet   A 3      amp IF  testsheet   C 4  lt   99  testsheet   A 4      amp IF  testsheet   C 5  lt   99  testsheet   A 5      amp IF  testsheet   C 6  lt   99  testsheet   A 6      amp IF  testsheet   C 7  lt   99  testsheet   A 7      amp IF  testsheet   C 8  lt   99  testsheet   A 8      amp IF  testsheet   C 9  lt   99  testsheet   A 9      amp IF  testsheet   C 10  lt   99  testsheet   A 10      amp IF  testsheet   C 11  lt   99  testsheet   A 11      amp IF  testsheet   C 12  lt   99  testsheet   A 12      amp IF  testsheet   C 13  lt   99  testsheet   A 13      amp IF  testsheet   C 14  lt   99  testsheet   A 14      amp IF  testsheet   C 15  lt   99  testsheet   A 15      amp IF  testsheet   C 16  lt   99  testsheet   A 16      amp IF  testsheet   C 17  lt   99  testsheet   A 17      amp IF  testsheet   C 18  lt   99  testsheet   A 18      amp IF  testsheet   C 19  lt   99  testsheet   A 19      amp IF  testsheet   C 20  lt   99  testsheet   A 20      amp IF  testsheet   C 21  lt   99  testsheet   A 21      amp IF  testsheet   C 22  lt   99  testsheet   A 22      amp IF  testsheet   C 23  lt   99  testsheet   A 23      amp IF  testsheet   C 24  lt   99  testsheet   A 24      amp IF  testsheet   C 25  lt   99  testsheet   A 25      amp IF  testsheet   C 26  lt   99  testsheet   A 26      amp IF  testsheet   C 27  lt   99  testsheet   A 27      amp IF  testsheet   C 28  lt   99  testsheet   A 28      amp IF  testsheet   C 29  lt   99  testsheet   A 29      amp IF  testsheet   C 30  lt   99  testsheet   A 30      amp IF  testsheet   C 31  lt   99  testsheet   A 31      amp IF  testsheet   C 32  lt   99  testsheet   A 32      amp IF  testsheet   C 33  lt   99  testsheet   A 33      amp IF  testsheet   C 34  lt   99  testsheet   A 34      amp IF  testsheet   C 35  lt   99  testsheet   A 35      amp IF  testsheet   C 36  lt   99  testsheet   A 36      amp IF  testsheet   C 37  lt   99  testsheet   A 37      amp IF  testsheet   C 38  lt   99  testsheet   A 38      amp IF  testsheet   C 39  lt   99  testsheet   A 39      amp IF  testsheet   C 40  lt   99  testsheet   A 40        I didn t type it  I just use   amp   symbol to combine arranged cell in excel  another file  not the file we are working on    Notice that     part1   IF  testsheet   C   part2   1 to 40  part3    lt   99  testsheet   A   part4   1 to 40  part5         amp    Enter part1 to A1  part3 to C1  part to E1  Enter     A1   in A2      C1   in C2      E1   in E2  Enter     B1 1   in B2      D1 1   in D2  Enter    A2 amp B2 amp C2 amp D2 amp E2   in G2 Enter    I1 amp G2   in I2   Now select A2 I2   and drag it down  Notice that the number did the increament per row added  and the generated text is combined  cell by cell and line by line    Copy I41 content  paste it somewhere  add       in front  remove the extra  amp  and the back    Result   code as you intended    I ve use excel OpenOfficeCalc to help me generate code for my projects  Works for me  hope it helps for others

User · Answer

Going to answer this myself  correct me if I m wrong    It is not possible to iterate over a group of rows  like an array  in Excel without VBA installed   macros enabled

User · Answer

The way to get the results of your formula would be to start in a new sheet    In cell A1 put the formula    IF  testsheet  C1  lt   99  testsheet  A1        Copy that cell down to row 40 In cell B1 put the formula    A1   In cell B2 put the formula    B1  amp  A2   Copy that cell down to row 40   The value you want is now in that column in row 40   Not really the answer you want  but that is the fastest way to get things done excel wise without creating a custom formula that takes in a range and makes the calculation  which would be more fun to do

[excel] How to loop in excel without VBA or macros?

The answer is

Examples related to excel

Examples related to excel-formula

Tags