Average Required With Dynamic Column Range

Question

Hi Experts,

I have large set of data, I wants to calculate average of data with dynamic column range, mean when ever i add data in next column, it automatically add average of said column.

below is the sample data and result with three examples, actual results columns are only two, which update on daily basis with addition of data in next column.

It goes till end of every month.

can you please guide any solution ?

Thanks In Advance

Rgds,

Waseem

Answer 1

In cells J1:K1, M1:N1 and P1:Q1 I have placed the number of columns that you want to calculate for each case of the table B3:H7 (look carefully at the screenshot I have placed above).

In cell J3 I have applied this formula:

=IFERROR(--TRANSPOSE(TEXTSPLIT(ARRAYTOTEXT(IFERROR(BYCOL(J1:Q1, LAMBDA(a, TEXTJOIN(";",, BYROW(CHOOSECOLS(B3:H7, SEQUENCE(a)), AVERAGE)))), "")), ";", ", ")), "")

Hope this helps.

Answer 2

Call the leftmost column of data to be averaged ldata, 2 in your sample.

Call the leftmost column of averages lavg, 10 in your sample.

Looking at the first average of each pair, the first computes columns 2 to 4, the next columns 2 to 5, then column 2 to 6, ... Expressed as an offset from ldata, it is ldata+2, ldata+3, ...

In other words, every time you move to the average two columns over, you process one additional column of data. Let cols denote the number of columns away from lavg. Then the rightmost data column to be processed is rdat = ldat+2+cols/2.

Looking at the second average of each pair, its rdat is always one less than the rdat of the first average. That is, subtract one whenever cols is odd.

These relationships yield the general formula

=LET(ldat,COLUMN($B3),lavg,COLUMN($J3),cols,COLUMN()-lavg,oddc,MOD(cols,2),rdat,2+ldat+INT(cols/2)-oddc,AVERAGE(INDIRECT(ADDRESS(ROW(),ldat)):INDIRECT(ADDRESS(ROW(),rdat))))

You need to change the values for ldata and lavg to match where your actual data is. Put the formula in the top left average cell and copy it down and to the right as far as needed.

If you intend to do this for every day in the month, then your averages have to start past column AE. This will probably preclude viewing the data and the averages simultaneously.

Answer 3

Hi Marcin

Thanks for your reply, it may be my incompetency that I am unable to understand your solution. Can you please guide me further.

 

In addition, when I use your formula in column J3, due to non-blank cell, J3 also included in calculation

Answer 4

Try the following:

Step 1: Use AVERAGE with dynamic column referencing

Let's say your data range starts at C3 (Day-1 row 1) and goes across columns (Day-2, Day-3…). For Result-1 (Average of Day-1 to current last column):

=AVERAGE($C3:INDEX($3:$3,COUNTA($3:$3)+2))

• $C3 → starting column (Day-1 data).
• INDEX($3:$3,COUNTA($3:$3)+2) → finds the last non-empty column in row 3.
• AVERAGE → takes average from Day-1 up to the latest column.

Step 2: Average for a fixed point (like Day-1 to Day-4, Day-1 to Day-5, etc.)

If you want the formula to always update to include only the latest column, just change the +2 part depending on your layout. Example for Result-2 (Day-1 to last column – 1):

=AVERAGE($C3:INDEX($3:$3,COUNTA($3:$3)+1))

Step 3: Dragging down for all rows

Once you set this formula in the first result cell (e.g., J3), just drag it down to apply to all rows. It will dynamically adjust for each row's range as you add new columns.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin