sum() and average() formula with dynamic columns

**umbata** · 05-25-2015, 11:06 AM

I've come across this formula in an excel spreadsheet that I'm working with:

=SUM(INDEX($A1120:$FI1120,COLUMN(Current_qtr)):$FI1120)

where current_qtr refers to column BO:BO (defined in the Name Manager)

I've never seen the SUM function used this way, but its useful because it allows the formula to be updated automatically with changing values of current_qtr. Can this approach be adapted to an array formula? such as:

={AVERAGE(IF(BO1150:FI1150<>0, BO1150:FI1150,""))}

i.e. how can current_qtr be used to replace the values of BO1150?

Thanks....

Don

**skywriter** · 05-25-2015, 12:29 PM

i.e. how can current_qtr be used to replace the values of BO1150?

BO1150 is only a single cell.
If what you really meant is how do I replace BO1150:FI1150 with current_qtr, then have you tried simply replacing that range in the formula with the named range and using Control + Shift + Enter to confirm the array formula?