Excel Average every 4 numbers

0
votes

In excel, I have a column of about 30,000 entries. I want to take that column and create a new one where the first entry will be the average of the first 4 entries in the original column. The 2 entry in the new column will be the average of the next 4 entries of the original column (from 5-8), so on and so forth. The new column then will therefore have 1/4 of the the size of the original column. How can you do this with excel? In the image you see the N column from where I want to calculate the averages. In the Q column you see the average of the first 4 values of the N column. I want to extend the Q column so the second entry would be the average of the next 4 values (from 5th to 8th values) and so on and so forth for the remainder of the N column. As said, the Q column will therefore have at the end 1/4 of the size of the N column.

enter image description here

2
excelwindowsformulaaverage
See How to Ask a quesiton with an minimal reproducible example. Include your own attempt at solving this and state where you are having difficulties. - JvdV
is it not clear? - Angel Ortiz
In fact, it's off-topic. Currently you ask if someone else can do the work for you. The answer is yes, but you'd have to hire someone. If you want help on this forum you'd have to follow those links I included to see how to ask a question on Stack Overflow. - JvdV
Is it better now? - Angel Ortiz

2 Answers

0
votes

@harun24hr has probably posted the right answer, but an alternative solution could be to do the following:

Merge 4 cells in the column you wnat to show the average and then calculate the average eg:

=AVERAGE(A1:A4)

excelcalculateaverage

0
votes

Try below formula.

=SUM(INDIRECT("N" & ROW()*4-3 & ":N" & ROW()*4))/4

ROW()*4 this will multiply every row by 4 means will create a series like 4, 8, 12 ... Now ROW()*4-3 will generate 1, 5, 9 ...

So, by this part of formula "N" & ROW()*4-3 & ":N" & ROW()*4 we will get N1:N4, N5:N8, N9:N12 and so on. INDIRECT() formula will redirect those ranges for SUM() formula and SUM formula will give you summation of of every 4 row data. Finally dividing by 4 will give you average.

Sample Screenshot