How to automatically repeat formulas when a new row is added in google sheets? Arrayforumla or script?

user_19534235 0
Viewed 40

I've been researching to find a solution for this but it seems I'll need some help. I'm trying to set up some formulas on a google sheet so when my VA adds a new row of information, my formulas will automatically populate the new rows.

https://docs.google.com/spreadsheets/d/1xapTzLPzi0v2ZMnOcOn81VF3MSGaV_j0bFuxIsdHNs4/edit?usp=sharing

Here is the spreadsheet of how it is now. I don't want my VA touching the grey columns because those have the formulas. But, they need to copy the formula every time they add a new row. The spreadsheet currently has the formulas that need to be manually copied.

I've tried the arrayforumla but I can't seem to get it to populate the rows with the correct number. It formulates row 2 and then repeats for every row. This was the arrayformula for Column B I had made and placed in the header:

={"10%+";ARRAYFORMULA(IF(K2:K="",,SUM(K2*(1+10%))))}

I've looked into app scripts but the code is confusing and when I implement a script I found on here, it doesn't work.

I'd really appreciate it if someone can give me some guidance on what I'm doing here. Columns/formulas I want to automatically fill in are: B, C, G, H, I, K.

google-sheets-formula google-apps-script google-sheets
1 Answers

Here are the formulas you can use:

B: =A2*(1+10%) >> ={"10%+"; arrayformula(A2:A*(1+10%))}
C: =A2*(1-10%) >> ={"10%-"; arrayformula(A2:A*(1-10%))}
G: =F2+1       >> ={"Total Competitive Sellers"; arrayformula(F2:F+1)}
H: =D2/G2      >> ={"Sales Equity (Units/Mo)"; arrayformula(D2:D/G2:G)}
I: =E2/G2      >> ={"Sales Equity (Revenue/Mo)"; arrayformula(E2:E/G2:G)}
K: =H2*J2      >> ={"Estimated Net/Mo"; arrayformula(H2:H*J2:J)}

These account for headers, so you can place them in the first row. They will autofill down the column.

You could also wrap the calculations in IF statements so that rows without a data entry stay blank:

B: =A2*(1+10%) >> ={"10%+"; arrayformula(if(A2:A="", "", A2:A*(1+10%)))}
C: =A2*(1-10%) >> ={"10%-"; arrayformula(if(A2:A="", "", A2:A*(1-10%)))}
G: =F2+1       >> ={"Total Competitive Sellers"; arrayformula(if(A2:A="", "", F2:F+1))}
H: =D2/G2      >> ={"Sales Equity (Units/Mo)"; arrayformula(if(A2:A="", "", D2:D/G2:G))}
I: =E2/G2      >> ={"Sales Equity (Revenue/Mo)"; arrayformula(if(A2:A="", "", E2:E/G2:G))}
K: =H2*J2      >> ={"Estimated Net/Mo"; arrayformula(if(A2:A="", "", H2:H*J2:J))}
user_118612180
Related