Need help in excel formulas-CodePudding

I have formulas for the top 4 rows formula is as follows row 1) C1= A1 B1 row 2 ) C2=A2 B2 row 3) C3=A3 B3 row 4) C4=C3/C1 and the next row is blank ie C5 post which the same continues 4 rows has formula and the 5th row is blank I have a huge data how to copy the formula below please help

Am struck please someone assist I have skipped the blanks and copied the first four rows selected the below rows and pasted I need any other alternative either by formula or by copy paste

CodePudding user response：

The easiest way to do this on a very large scale is to just have ONE and ONLY one formula that applies to all cells, so you are copying it all the way down the sheet one time.

I'd do this by looking at ROW() to get the row number, and more specifically, using MOD(ROW(),5) to get a row index that goes 0-4, with 0 being the blank row.

This formula will look back up to previous rows so you will have to "prime the pump" for the first 3 rows manually:

Cell C1 will be =A1 B1
Cell C2 will be =A2 B2
Cell C3 will be =A3 B3
Cell C4 will be =SWITCH(MOD(ROW(),5),1,A4 B4,2,A4 B4,3,A4 B4,4,C3/C1,0,"")

Then copy this cell, C4, down the entire length of the sheet.

My specific formula assumes there's no headers and the values start in row 1. If not, you just have to move the formulas to the right SWITCH indexes 0-4.

CodePudding user response：

Cell C1

= IFS(MOD(ROW(),5)=0, "", MOD(ROW(),5)=4, OFFSET(C1, -1, 0)/OFFSET(C1, -3, 0), TRUE, A1 B1)

You can calculate all your data using that formula simply. Just double-click or drag the fill handle of cell C1 down to copy the formula.