Calculate the average and standard deviation for every other cell in a column

[wyatt]

Hello. I need to calculate the average and standard deviation for every other cell in a column (D9:D81). The odd cells contain the data I am interested in. The even cells contain times that I need to ignore. Some of the odd cells will be blank, and I need those to not affect the results of the equation. I have read several posts here and elsewhere that include MOD and ROW functions, but I can't seem to get those to work. Any help would be appreciated.
Thanks,
Wyatt

[rcm]

one way of doing it, just copy the formula in the b column...

[Tony Valko]

Try these array formulas**:

=AVERAGE(IF(MOD(ROW(D9:D81)-ROW(D9),2)=0,IF(D9:D81<>"",D9:D81)))

=STDEV(IF(MOD(ROW(D9:D81)-ROW(D9),2)=0,IF(D9:D81<>"",D9:D81)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[wyatt]

Thank you both for your quick replies.

RCM, I was hoping to find a solution that didn't involve adding extra columns. Thank you for your suggestion.

Tony, this is exactly what I was looking for. I didn't have the second IF statement in my previous attempts. What is "=0,IF(D9:D81<>"",D9:D81)))" actually doing?

[Tony Valko]

The 1st IF statement checks the row numbers of the range and makes sure we get the correct rows for the calculation. Something you may not have thought about... if you were to insert new rows before the target range then the row numbers for the target range might end up being the even numbered rows. This is accounted for in the 1st IF statement.

The 2nd IF statement is checking that the cells actually contain some entry. We use this test to exclude the empty cells from the calculation.

[wyatt]

Ok. Thank you for explaining.

[Tony Valko]

You're welcome!

[wyatt]

Now that I have the formula for averaging every other cell in a selection, I need to expand the average function to include additional columns. Using the formula that you provided, Tony, I am taking the average of D9:D85, G9:G85, and several others. I would like to calculate an overall average of the multiple ranges. Can you (or someone) help me out?

[Tony Valko]

Tell us what ALL the ranges are.

[wyatt]

I thought it would be simpler if you showed me with two and then I could add the rest. But here are all of them:
D9:D85 G9:G85 J9:J85 M9:M85 P9:P85 S9:S85 V9:V85 Y9:Y85
I will need average and standard deviation, though I believe the formulas will be identical except for the function at the beginning.

[Tony Valko]

I needed to know all of the ranges so I could determine if there is a pattern. Your pattern is every 3rd column.

Try this:

=AVERAGE(IF(MOD(ROW(D9:Y85)-ROW(D9),2)=0,IF(MOD(COLUMN(D9:Y85)-COLUMN(D9),3)=0,IF(D9:Y85<>"",D9:Y85))))

Still array entered.

[wyatt]

That's fantastic. Thank you so much.

[Tony Valko]

You're welcome. Thanks for the feedback!