Tuesday, December 26, 2017

yogi_Compute Average Of Values In Column M (row 3 to 63) And Then Every 4th Column Indefinitely To The Right

Google Spreadsheet   Post  #2328

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-26-2017
Multiple Cell Average Array
I currently have a spreadsheet (I will attach a copy) that is currently in order by years, and each year has 4 columns underneath it (Month to Date, % of Gross Month to Date, Year to Date, and % of Gross Year to Date). I have also created a section of 4 columns (columns E:H) that average up all previous years.

Is there a way to create an average array in E3 that does this for me instead of writing '=average(I3,M3,Q3,U3...)"? For example, I use the simple function "=ArrayFormula(if(E3:L11="","",minus(E3:L11,E15:L23)))" on a different sheet that works wonders subtracting values for an entire range for me.

At the end of every month, I currently add 4 more columns, import that data, and then have to add back in the I3, drag down the formula, reformat it, etc, and have to do so for multiple departments. Any help would be great appreciated!!!