Forum Discussion
Use Formula for Cell Reference - Example: Sliding Window Average
Is it possible to use a formula in a cell reference? For example, 'sum of the last 12 cells'. So the cell address might be =SUM($C$(ROW(THIS.CELL)-12):C19) I know this gets into relative cells, but...
=IF(COUNT($C$2:$C$2000)<12,SUM(INDIRECT("C2:C"&LARGE(IF(NOT(ISBLANK($C$2:$C$2000)),ROW($C$2:$C$2000)),1)))/COUNT($C$2:$C$2000),SUM(INDIRECT("C"&LARGE(IF(NOT(ISBLANK($C$2:$C$2000)),ROW($C$2:$C$2000)),1)-11):INDIRECT("C"&LARGE(IF(NOT(ISBLANK($C$2:$C$2000)),ROW($C$2:$C$2000)),1)))/12)This formula returns the expected result in my sheet. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
=IF(COUNT($C$2:C2)<12,SUM(INDIRECT("C2:C"&LARGE(IF(NOT(ISBLANK($C$2:C2)),ROW($C$2:C2)),1)))/MIN(ROW(C2)-1,12),SUM(INDIRECT("C"&LARGE(IF(NOT(ISBLANK($C$2:C2)),ROW($C$2:C2)),1)-11):INDIRECT("C"&LARGE(IF(NOT(ISBLANK($C$2:C2)),ROW($C$2:C2)),1)))/12)This formula returns the results if you want to copy the formula down from row 2. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.