Average every 5th non-empty/blank column

[jacedelaney]

Hi,

I have a dataset where I need to average every 5th column. However, I have missing values in some columns, which disrupts the average when using the normal formula method to look at every nth row:

(=AVERAGE(IF(MOD(COLUMN($A$1:$O$1),2)=0,$A$1:$O$1,0))

I would like the formula to only take a cell into account if it has a non-zero value, and is therefore non-empty. Can anyone help with this please?

Cheers.

[vlady]

maybe.

=AVERAGE(IF(($A$1:$O$1<>"")*(MOD(COLUMN($A$1:$O$1),2)=1), $A$1:$O$1,0))


***Not tested

[Glenn Kennedy]

If the cell is EMPTY, use this:

=AVERAGE(OFFSET($A$1:$O$1,(COLUMNS($A:A)-1)*5,))

[Glenn Kennedy]

Ooops. here's a file...

[samba_ravi]

Pls attach sample excel file with expected result

[Glenn Kennedy]

I'm not awake yet, it seems. Disregard the alst file. The formula was OK, but the example wasn't.

If the cells are EMPTY, use this ordinary formula:
=AVERAGE(OFFSET($A$1:$O$1,(COLUMNS($A:A)-1)*5,))

If the cells actually contain a zero, use this arry formula:
=AVERAGE(IF(MOD(COLUMN(A1:O1)-COLUMN(A1),5)=0,IF(A1:O1>0,A1:O1)))


Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[jacedelaney]

Hi Glenn,

Thanks for your quick reply! Yep that's almost there, but the formula you used does not exclude values inputted into the 2nd, 3rd, 4th and 5th columns. I've attached any example of how I have done this previously, but now that I have so much data, simply clicking each cell was way too time consuming.

To give this better context, I want to find the average and standard deviation of 5 different variables across a number of time points, which are delivered horizontally using a pivot table. Please see the attached example.

Thanks again!

[newdoverman]

I see this 2 different ways.
1. The values to be averaged are in B3, G3 AND L3

Please Login or Register  to view this content.

2. The values to be averaged are in F3, K3 AND P3

Please Login or Register  to view this content.

BOTH FORMULAE ARE ARRAY FORMULAE. ENTER WITH CTRL + SHIFT + ENTER

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		Day 1					Day 2					Day 3
2		Var1	Var2	Var3	Var4	Var5	Var1	Var2	Var3	Var4	Var5	Var1	Var2	Var3	Var4	Var5
3	Name:	1	2	3	4	5	1.2	2.3	3.2	4.1	5.6	1.1	2.4	3.3	4.3	5.6
4
5	1	1.1
6	2	5.4

[jacedelaney]

Thanks for that! We are almost there, I apologize but I forgot to include that there will be some missing values in some of the cells (i.e. no data entry on a particular time point). Please see new example attached.

[newdoverman]

Perhaps this is what you are looking for.
For the Average, enter this in S3 and fill across.

Please Login or Register  to view this content.

For the STDEV enter this in X3 and fill across.

Please Login or Register  to view this content.

Both formulae are ARRAY formulae so enter with Ctrl + Shift + Enter

	S	T	U	V	W	X	Y	Z	AA	AB
1	Average					StDev
2	Ave1	Av2	Ave3	Ave4	Ave5	Stdev1	Stdev2	Stdev3	Stdev4	Stdev5
3	1.1	2.2	3.2	4.3	5.4	0.1	0.2	0.2	0.4	0.3

[Glenn Kennedy]

Yea - that's it. NDM's formula is spot on, but the displayed values for Var 4 (4.3 and 0.4) aren't correct. The formula does, however, give the correct answers (4.1 & 0.2).