averaging every nth column working for all but 1 case

[bjcowen9000]

I attached a file. I have a long column in D which I want to average every 21 values.

You can see the formula in column I.
In I2, I average every 21st value in D starting in D2
In I3, I average every 21st value in D starting in D3

Why is my last one giving me the #DIV/0! error? It should correspond to every 21st value starting in D21

[MarvinP]

Hi bj,

I think it is because you don't have a proper If statement. You don't tell what to return if true and what if false. Try this instead.. Using CSE when entered

=AVERAGE(IF(MOD(ROW(D2:$D$1953),21)=ROW($D2),D2:$D$1953),D2:$D$1953,0)

[bjcowen9000]

Hi bj,

I think it is because you don't have a proper If statement. You don't tell what to return if true and what if false. Try this instead.. Using CSE when entered

=AVERAGE(IF(MOD(ROW(D2:$D$1953),21)=ROW($D2),D2:$D$1953),D2:$D$1953,0)

This still confuses me. How can I create a formula like this for each case?

[shukla.ankur281190]

Try

=AVERAGE(OFFSET(D1,1,,21))

[bjcowen9000]

Try

=AVERAGE(OFFSET(D1,1,,21))

I do not believe this works. The values are not correct.

[FDibbins]

Because you included your file here, I will close your other thread, so you can continue here.

The reason for your error if easy to see if you put the MOD and ROW() formulas in their own cells...

	E	F
2	2	2
3	3	3
4	4	4
5	5	5
6	6	6
7	7	7
8	8	8
9	9	9
10	10	10
11	11	11
12	12	12
13	13	13
14	14	14
15	15	15
16	16	16
17	17	17
18	18	18
19	19	19
20	20	20
21	0	21
22	1	22

E2=MOD(ROW(D2:$D$1953),21)
F2=ROW($D2)
both copied down.

As you can see MOD never gets higher than 20, but ROW just keeps incrementing.

Did you see my suggestion in your other thread...
J2=AVERAGE(D2,D21)

[bjcowen9000]

How could averaging 2 numbers possibly work? I have a very long column of numbers, and it seems like that would just average 2 numbers. If you look in column A, I have 20 values for each set, corresponding from 1 to 20.

I want to average the values in column D that correspond to each A column value of 1. Then I want to do it for 2 etc.

[FDibbins]

How could averaging 2 numbers possibly work? I have a very long column of numbers, and it seems like that would just average 2 numbers. If you look in column A, I have 20 values for each set, corresponding from 1 to 20.

I want to average the values in column D that correspond to each A column value of 1. Then I want to do it for 2 etc.

Apologies, TOTALLY misread that 1

[AliGW]

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

http://www.mrexcel.com/forum/excel-q...er-column.html

[Glenn Kennedy]

AM I missing something... or is the answer not simply:

=AVERAGEIF(B:B,H2,D:D)

[bjcowen9000]

I ended up using:

=AVERAGEIF($A$1:$A$3480,"=1",$B$1:$B$3480)

How could I modify instead of taking the average, I get the stdev of those numbers it included for each average?

[MarvinP]

Hey BJ,

See the attached where I've used the formula I presented above only changing the first formula. It also shows why you get a #Div/0 error.

Every 21st Number Avg Cnt Stdev CSE.xlsx