How to Make FOR Loop in Excel Using Formula (3 Examples)

• Here, to make you understand the algorithm easily, I will explain the whole thing with two reference cells: cells C11 and C17. In cells C11 and C17, the values are 10 and 20 respectively (above image). If you are used to Excel formulas, then you can smell the OFFSET function, as OFFSET function works with reference points.
• Now, imagine I am taking the values of cell ranges C8:C11 & C11:C14, and C14:C17 & C17: C20 side by side [image below]. Reference cells are C11 and C17 and I am taking a total of 7 cells around the reference cell. You will get an imaginary picture like the following. From the first part, you can find a pattern from the image. C9–C12=3, C10-C13=3, there is a pattern. But for the second part, there is no such pattern.

• So, let’s build the algorithm with keeping the above pattern in mind. Before building the common formula, I shall show what the formulas will be for the cells C11 and C17 and then will modify the formula to make it common for all. For a reference point (like C11 or C17), I shall take a total of 7 cells around it (including the reference point) and place them side by side in the formula creating arrays. Then I shall find out the difference of the arrays if any of the differences is equal to 3 that the reference cell will be TRUE valued.
• Here, I can do that easily using the OFFSET function as the OFFSET function returns an array. Say for cell reference C11, I can write the formula like this: =OR(OFFSET(C11, 0, 0, 4, 1)-OFFSET(C11, -3, 0, 4, 1)=3). What will this formula return? The first offset function of the formula will return array: {10; 11; 12; 15}, second offset function will return array {5; 8; 9; 10}. And you know {10; 11; 12; 15} – {5; 8; 9; 10} = {10-5; 11-8; 12-9; 15-10} = {5; 3; 3; 5}. When this array is logically tested with =3 then Excel calculates internally like this: {5=3; 3=3; 3=3; 5=3} = {False; True; True; False}. When the OR function is applied on this array: OR({False; True; False; True}, you get TRUE. So cell C11 gets true values as returned.
• So, I think you have got the whole concept of how this algorithm is going to work. Now there is a problem. This formula can work from cell C8, above cell C8, there are 3 cells. But for cells C5, C6, and C7 this formula cannot work. So the formula should be modified for these cells.
• Now, for cells C5 to C7, we want that the formula will not take into consideration the upper 3 cells. For example, for cell C6, our formula will not be like the formula for cell C11: =OR(OFFSET(C11, 0, 0, 4, 1)-OFFSET(C11, -3, 0, 4, 1)=3).
• Here, for cell C5, the formula will be like: OR(OFFSET(C5, 3, 0, 1, 1)-OFFSET(C5, 0, 0, 1, 1)=3).
• Then, for cell C6, the formula will be like: OR(OFFSET(C6, 2, 0, 2, 1)-OFFSET(C6, -1, 0, 2, 1)=3).
• After that, for cell C7, the formula will be like: OR(OFFSET(C7, 1, 0, 3, 1)-OFFSET(C7, -2, 0, 3, 1)=3).
• Again, for cell C8, the formula will be like: OR(OFFSET(C8, 0, 0, 4, 1)-OFFSET(C8,-3, 0, 4, 1)=3); [this is the general formula].
• Then, for cell C9, the formula will be like: OR(OFFSET(C9, 0, 0, 4, 1)-OFFSET(C9,-3, 0, 4, 1)=3); [this is the general formula].
• Finally, do you find some patterns from the above formulas? The first OFFSET function’s rows argument has decreased from 3 to 0; the height argument has increased from 1 to 4. The second OFFSET function’s rows argument has decreased from 0 to -3 and height argument has increased from 1 to 4.
• Firstly, the first OFFSET function’s rows argument will be modified like this: MAX(ROW(C$5)-ROW(C5)+3,0)
• Secondly, the second OFFSET function’s rows argument will be modified like this: MAX(ROW(C$5)-ROW(C5),-3) 
• Thirdly, the First OFFSET function’s height argument will be modified like this: MIN(ROW(C5)-ROW(C$5)+1,4)
• Fourthly, the second OFFSET function’s height argument will be modified like this: MIN(ROW(C5)-ROW(C$5)+1,4)
• Now, try to understand the above modification. These are not that tough to understand. All these four modifications are working as FOR LOOP of Excel VBA but I’ve built them with Excel Formulas.
• So, you got the ways how the general formula works for the cells from C5:C34.

Formula Breakdown

Here, the OR function will return TRUE if any of the given logic becomes TRUE.

• Firstly, B5=”” is the 1st logic, which will check whether the cell B5 contains any value or not.
• Secondly, C5=”” is the 2nd logic, which will check whether the cell C5 contains any value or not.
• Thirdly, D5=”” is the 3rd logic. Similarly, which will check whether the cell D5 contains any value or not.

Now, the IF function returns the result which will fulfill a given condition.

• When the OR function gives TRUE then you will get “Info Missing” as Status. Otherwise, you will get “Done” as the Status.

Formula Breakdown

• Here, $C$5:$C$13 is the data range from which the SUMIFS function will do the summation.
• Then, $B$5:$B$13 is the data range from where the SUMIFS function will check the given criteria
• Lastly, E7 is the criteria.
• So, the SUMIFS function will add the payments for the E7 cell value.