Find average of every other row of a column

[mv8167]

I have the following code that adds up a column and divides the total by the count found that are found to have valid values:

=SUMIF(L1:L10,"<>""")/COUNT(L1:L10)

I need my code to look at every other row. If a value exists, then add to the sum, plus add one to the count to fgind the average.

There are 10 values in one column, for rows 2, 4, 6, 8 and 10 if a value exists, sum qand add 1 to the count.

Is this possible? Thx

Lorena

 

[MrVillareal]

Hi

Can you try this one.

Excel Workbook
	K	L
1		100
2		200
3		300
4		400
5		500
6		600
7		700
8		800
9		900
10		1000
11	Average	600
Sheet15

Excel 2010

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[mv8167]

Thx Mr Villareal

I tried this buit I get 0.0 as my answer.;

My column is as such:

1:00
1:30
1:05


1:05
1:05

0:35
0:40

I need to add (1:30+1:05+0:40)/3 = 1:05


Go Manny Paccio! Go Philippines

 

[MrVillareal]

Its an array formula you should Ctrl Shift Enter.

Tnx for feedback.

 

[MrVillareal]

Try this its a knockout punch.LOL.

Excel Workbook
	K	L
1		1:00
2		1:30
3		1:05
4
5
6		1:05
7		1:05
8
9		0:35
10		0:40
11	Average	1:05
Sheet15

Excel 2010

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[Aladin Akyurek]

If there are empty or text-valued cells in-between, an ISNUMBER test is more appropriate...

Control+shift+enter, not just enter:

=AVERAGE(IF(MOD(ROW($L$1:$L$10)-ROW($L$1)+1,2)=0,IF(ISNUMBER($L$1:$L$10),$L$1:$L$10)))

 

[mv8167]

Ok, I did it once and the code worked.

I am lost on why I need to Cftrl-Shift-Enter? I then try it again it does not work? Within Excel, I copy/paste your code with no luck also.

Is this av spercial code that only works one time, lol

 

[MrVillareal]

Wow tnx for info Aladin..thats more efficient.

 

[mv8167]

Aladin,

Thx also for your time.

Ive never used Cntrl-Shift-Enter before. Do I Cntrl-C first, then Cntrl-Shift-Enter into the cell or command line (fx)? I did this once but know my Cntrl-Shift-Enter dose not seem to work. lol

 

[Aladin Akyurek]

Aladin,

Thx also for your time.

Ive never used Cntrl-Shift-Enter before. Do I Cntrl-C first, then Cntrl-Shift-Enter into the cell or command line (fx)? I did this once but know my Cntrl-Shift-Enter dose not seem to work. lol

The combined use of the control+shift+enter keys signal Excel that it must treat the formula as one that operates on arrays. See the Help file on "arrays".

Steps:

1) Type or copy the formula to the relevant cell.

2) While still in the cell, press down the control and shift keys together and hit the enter key at the same time.

If done properly, the formula is surrounded by a pair of { and } by Excel itself.

3) Copy down the formula using AutoFill.