Simple Help on Rolling Averages (with special attributes)

[saxonomy]

Hello

I have the following data:

Jan05 Feb05 ..... Dec05 1Q05 2Q05 3Q05 4Q05 Jan06 Feb06 Mar06 Apr06 May06 Rolling12
FirmA 40 50 44 92 18 66 42 100 55 13 83 44
FirmB 70 75 41 77 38 73 28 132 45 37 93 75


If you can, please....how do I calculate a rolling 12 monthly average if the columns that are relevant are not necessarily consecutive i.e. I only want the months to be considered, not quarterly information (1Q05, 2Q05, etc.)

Any help will be greatly appreciated.

Wole

 

[SkipVought]

Hi,

Do you have REAL DATES in the headings?

Skip,

_[red][/red]

 

[saxonomy]

No,

I have them as 'Jan 06, 'Feb 06, etc.

 

[mintjulep]

Something along the lines of:

=(sum(B2:B14)-sumif(B2:B14,instr(A2:A14,"Q")))/12

Ugly, and I'm sure the syntax is wrong.

 

[SkipVought]

I'd first suggenst using REAL Dates and FORMATTING as you want them displayed.

I'd push the aggregations off to the right, out of the way, or on a separate row..

Skip,

_[red][/red]

 

[saxonomy]

Thanks !!!!

 

[SkipVought]

mint,

???

ROLLING dates ACROSS...

the 12 months is ACROSS the sheet, like B22.

but even at that, how can you be sure that there will ALWAYS be FOUR columns with quarter data in that relative range?

sax,

use the subtotal function for summing the quarter data. That way you will not need to subtract out the columns with those subtotals.

Skip,

_[red][/red]