Average of every 18th cell in a column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Piotr - 06 May 2008 17:18 GMT

Hello!

My spreadsheet looks like this:

    Column D

    value1
    value2
    value3
    ...
    Average1 of 1,2,3... (cell D25)

    value4
    value5
    value6
    ...
    Average2 of 4,5,6... (cell D43)...

Every 18th row, starting from D25 there is a "partial" average. Now, I
want to calculate the total average from them, but...

It is a template, so there actually is a value in cell D25 and D43
(25+18), but in the following cells, there only is the formula for
average calculation of future entries. In cells D61 and further, there
is an error message (dividing by 0).

What would be the formula to calculate an average from every 18th cell,
starting with D25, ending by, say, D2000, and considering only the cells
with an actual value?

Thanks in advance!
Peter

Reply to this Message

Peo Sjoblom - 06 May 2008 17:37 GMT

One way

=SUMPRODUCT(--(MOD(ROW(D25:D500),18)=7),--(D25:D500<>""),D25:D500)/SUMPRODUCT(--(MOD(ROW(D25:D500),18)=7),--(D25:D500<>""))

Signature

Regards,

Peo Sjoblom

> Hello!
>
[quoted text clipped - 28 lines]
> Thanks in advance!
> Peter

Reply to this Message

Guillermo_Lopez - 06 May 2008 17:44 GMT

> Hello!
>
[quoted text clipped - 28 lines]
> Thanks in advance!
> Peter

If you absolutely cannot restructure the spreadsheet, maybe this could
work:

= SUM(D7:D24,D26:D42,D44:D61, <etc>)/COUNT(D7:D24,D26:D42,D44:D61,
<etc>)

Count will not count Null fields. I think there is a Limit on the
number of Arguments in the SUM and COUNT functions, so to include the
112 segments you might have to split the functions: (SUM(...)+SUM(...)
+SUM(...)+<etc>)/(Count(...)+Count(...)+Count(...)+<etc>).

If you can restructure the spreadsheet, you cna add an extra column
(hide it) and use =D7 for cell (E7) and ignore the avg cells. Then
Average that on the D2000 cell. AVG (E7:E1999).

- GL

Reply to this Message

Piotr - 07 May 2008 00:21 GMT

Thanks to you two!

I used the hidden column method, very simple! You can always find a free
column, be it XFD column ;). And then I just referenced the result to
where I want it in my spreadsheet.

Greetings form Poland
Peter

Reply to this Message