I have a spreadsheet that I use every week to track sales of an item for 5 weeks. First week of sales goes in Column E, second week goes in Column F, etc.
Column J has the # of locations that carry that particular item.
Column K is my average $$ per location :The sales of the week (Column E,F,G,H or I- depending) divided by the # of locations (Column J).
Every week I have to change the formula in Column K to correspond to whatever week I am in for that particular item. Ex =E1/J1 for the first week, next week F1/J1, the next week G1/J1, then next week H1/J1, then the final week I1/J1.
Is there a script that would automatically divide the latest week entry without me having to update the formula in Column K every week for every row? I have hundreds of items, and changing the formula in every row in Column K is very time consuming.
Would a Javascript work? Something like: Divide I by J. if cell I is null, divide H by J. If H is null, divide G by J. If G is null, divide F by J. If F is null, divide E by J.
Best Answer
Short answer
Try
Explanation
The desired calculation could be done by using formulas. As the scripts run on Google Servers, if you are able to do something with formulas try them first as usually they will do calculations faster than a script that does the same.
It's worth to say that you could create custom functions and do some automations by using Google Apps Script1.
1: Extending Google Sheets