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

• In cells C11 and C17, the values are 10 and 20, respectively.
• We’re taking the values of cell ranges C8:C11 and C11:C14, and C14:C17 and C17:C20 side by side [image below]. Reference cells are C11 and C17 and we’re 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.

• Before building the common formula, we’ll 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), we need a total of 7 cells around it (including the reference point) and place them side by side in the formula creating arrays. Then we find out the difference of the arrays. If any of the differences is equal to 3, the reference cell will be TRUE-valued.
• For cell reference C11, we 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 the array: {10; 11; 12; 15}, the second offset function will return the array {5; 8; 9; 10}. After that: {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 Excel calculates {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. Cell C11 gets TRUE.
• This formula can work from cell C8, since there are 3 cells above it. But for cells C5, C6, and C7 this formula cannot work. For cells C5 to C7, the formula will not take into consideration the upper 3 cells.
• For cell C5, the formula will be: OR(OFFSET(C5, 3, 0, 1, 1)-OFFSET(C5, 0, 0, 1, 1)=3).
• For cell C6, the formula will be: OR(OFFSET(C6, 2, 0, 2, 1)-OFFSET(C6, -1, 0, 2, 1)=3).
• For cell C7, the formula will be: OR(OFFSET(C7, 1, 0, 3, 1)-OFFSET(C7, -2, 0, 3, 1)=3).
• For cell C8, the formula will be: OR(OFFSET(C8, 0, 0, 4, 1)-OFFSET(C8,-3, 0, 4, 1)=3) (this is the general formula).
• 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.
• The first OFFSET function’s rows argument will be modified like this: MAX(ROW(C$5)-ROW(C5)+3,0)
• The second OFFSET function’s rows argument will be modified like this: MAX(ROW(C$5)-ROW(C5),-3) 
• The first OFFSET function’s height argument will be modified like this: MIN(ROW(C5)-ROW(C$5)+1,4)
• The second OFFSET function’s height argument will be modified like this: MIN(ROW(C5)-ROW(C$5)+1,4)
• All these four modifications are working as FOR LOOP of Excel VBA but built with Excel Formulas.

Formula Breakdown

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

• B5=”” is the 1st logic, which will check whether the cell B5 contains any value.
• C5=”” is the 2nd logic, which will check whether the cell C5 contains any value.
• D5=”” is the 3rd logic. It will check whether the cell D5 contains any value.
• The IF function returns the result which will fulfill a given condition.
• When the OR function gives TRUE, you will get “Info Missing” as Status. Otherwise, you will get “Done” as the Status.

Formula Breakdown

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