How to solve Repeated column in Grails mapping for entity?

Average of every nth column

• I have a spreadsheet in which i have need to calculate the average of every nth Column. The repeated columns are weekly sales data as follows - Units / Sales / GP Each week i will add another set of these columns for the weeks sales data and need to calculate the averages. I have found the following solution - =AVERAGE(IF((A1:Z1<>"")*(MOD(COLUMN(A1:Z1),2)=1),A1:Z1)) however this formula requires ctrl+shift+enter to be pressed in order to update. I have over 300rows in which i need to apply the formula, and as it will need to be updated weekly, so i don't think the above solution is ideal. Is there a way to solve this problem, that does not require a macro or the use of ctrl+shift+enter? Many Thanks Chris

• Answer:

  Chris, once you enter the formula with Ctrl+Shift+enter in the first cell where it will be used (assume row 1), then click the cell again. The cell will be highlighted and on the lower right corner wil be a little square as part of the highlight. If you double click that little black square, the array formula should be filled down the column. Athe A1:Z1 reference will be changed to A2:Z2 in the next row and so forth. Alternately, you can click on that black square and hold the button down, then just drag the mouse down which will copy the formula down the column while you move the mouse down. That should minimize the impact of the need to put the formula in 300 rows since you only need to enter it once when you are modifying it and then rapidly copying it to other cells. (you can also do copy and paste if you wish. copy the cell with the formula. Select all the other cells. Do paste). All that said, you formula appears to exclude cells that are blank. Perhaps you can just expand the range being examined to include future columns which are not yet in used. for example, I put data in A1:H1 and used our formula and it gave me the correct average for just the cells that contained data. Columns beyond H were ignored in the calculation.