average of everytwo rows?

[scottnshelly]

i have a workbook that runs from 5:103. every third row is an average
row. then in row 104 i have a grand average. i've tried doing
something like =average(c5,c6,c8,c9,c11,c12...) all the way, but it
only allows 30 arguments.
is there a better way to get the grand average?

sorry if this doesn't make much sense, if it is hard to understand, i
will try to elaborate

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=AVERAGE(IF(MOD(ROW(C5:C103)-1,3)=0,C5:C103))

 

[Niek Otten]

Knowing Franks capabilities, I immediately assume this does what the OP
asked for.
That is not necessarily the same as what the OP really needed; the average
of the intermediate averages is not the same as the average of all
underlying figures.

Of course I leave it to the OP to define what is required.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Frank Kabel]

Hi Niek
I agree with you. This may NOT be the expected result but just works as
requested <ebg>.
So wrong business spec -> wrong coding

 

[ForSale]

allow me to elaborate.
as stated previously, i have information on rows 5:103. everythird ro
is an average of the previous two rows.
here's an example

c5
90
c6
80
c7
=avg(c5:c6)

and so on all the way through 103. so i some how need a grand averag
of all of the actual scores. it doesn't matter if this is gotte
through the scores, or the averages, but i need C104 to tell me th
average of the averages.
hope this makes more sense.
thank

 

[Frank Kabel]

Hi
so either you want to average the averages. Then use the array formula
(entered with CTRL+SHIFT+ENTER):
=AVERAGE(IF(MOD(ROW(C7:C103)-7,3)=0,C7:C103))

or for averaging the individual values use the array formula:
=AVERAGE(IF(MOD(ROW(C5:C103)-1,3)<>0,C5:C103))

 

[ForSale]

Thanks for the prompt response
I copied and pasted both formulas that you've given and they both giv
me #Div/0! error
what am i doing wrong

 

[Myrna Larson]

Hi, Frank:

Looking at his sample data, I assumed he wants to skip the cells with average
formulas. So you should include two of every 3 values. To make the formula
match his description, I think you should change =0 to <>0.

But it doesn't really matter <g>.

I just put 30 random numbers in A1:A30, then inserted the average formulas at
A3, A6, A9, etc.

In column B I replicated the original numbers via this formula:

=IF(MOD(ROW(),3)<>0,A1,"")

In column C, I replicated just the averages with =IF(MOD(ROW(),3)=0,A1,"")

Then I did averages on all 3 columns. The results were identical.

If you do SUMs on the 3 columns, you see that

sum of column B (the raw data) = X
sum of column C (the averages) = X/2
sum of column A (the raw data and averages) = X * 3/2

Sum of the raw data = sum of column A * 2/3. Number of items to be counted is
2/3 of the total numbers, so

(SUM(A1:A45)*2/3) / (COUNT(A1:A45)*2/3)
= SUM(A1:A45)/COUNT(A1:A45)
= AVERAGE(A1:A45)

Of course the above is correct only if each average consists of the same
number of cells. It isn't correct if one average includes 4 cells, another 2,
etc.

I expect Harlan will tell us that this should have been obvious from the start
<g>.

 

[Frank Kabel]

Hi
enter them with CTRL+SHIFT+ENTER!

 

[Frank Kabel]

Hi Myrna

But it doesn't really matter <g>.

agree. Should have thought about this before
probably too late over here.

[..]

Of course the above is correct only if each average consists of the same
number of cells. It isn't correct if one average includes 4 cells, another 2,
etc.

I expect Harlan will tell us that this should have been obvious from the start
<g>.

Indeed but probably with more mathematical detail

Frank

 

[Paul Corrado]

=SUMPRODUCT((MOD(ROW(C5:C103)+2,3)<>0)*(C5:C103))/(COUNTA(C5:C103)*2/3)

Or

=SUMPRODUCT((MOD(ROW(C5:C103)+2,3)=0)*(C5:C103))/(COUNTA(C5:C103)/3)

 

[Myrna Larson]

Indeed but probably with more mathematical detail

Hopefully. I'm waiting (seriously!)

 

[ForSale]

Thanks to everyone. I used Myrna's formula.
Should i use the same concept to determine the amount of audits an
percent audited?
allow me to elaborate.
in 106 i have 'total # of audits' and this counts the number of audits
should not count the averages.
in 108 i have 'percent monitored'. i would imagine it needs to b
something like '=counta(two out of every three rows betwee
a5:a103)/counta(two out of every three rows between c5:c103)

thanks again to everyon

 

[Myrna Larson]

Are you saying that you have blanks within the data? Can you repeat what the
layout is? What are the columns and what's in each? For each group of 3 rows,
the 3rd is the average of rows 1 and 2. What data is in those first two rows?