Excel help with Average() formula

[virtualranger]

OK, I have a list of data for every sinlge day since 01 May 1961 to present. It displays the date in one column (dd/mm/yyyy) and amount in the second column. This is over 16000 rows of data. Basicaly I need to get the average amount per month, and year, for the whole 45 years.

i.e.

1961 1962 1963
Jan 1.34 2.45 5.67
Feb 2.9 3.21 3.45
Mar 3.67 6.45 7.56

Any ideas on the best way to do this apart from manually doing an Average formula in every single cell?

Thanks,
Jamie

 

[SkipVought]

Hi,

Let's say you name column A DATA, rDates and column B DATA rAmt.

On a new sheet enter this date in A2 and copy down thru the ending date (whatever that is)
[tt]
A2: May 1961
[/tt]
you will notice that this incriments by months for the first of each month.

in B2
[tt]
=sumproduct((rDates>=A2)*(rDates<A3)*(rAmt))/sumproduct((rDates>=A2)*(rDates<A3)*(1))
[/tt]



Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[xlbo]

mmmmm - gotta be a pivottable

Year as ROW field
Month as COLUMN field
AVERAGE of Amount as VALUE field

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

Let's say your data is in columns A and B.
[ul]
[li]Add two 'helper columns':[/li]
[ul]
[li]In column C, type [COLOR=blue white]=month(A2)[/color][/li]
[li]In column D, type [COLOR=blue white][/color][/li]
[/ul]
[li]Select columns A[/li]
[li]Go to Data > Pivot Table and Pivot Chart Report > Next > Next > Layout[/li]
[li]Drag Month to the Column section.[/li]
[li]Drag Year to the Row section.[/li]
[li]Drag amount to the Data section.[/li]
[li]Double click on Count of amount.[/li]
[li]Change the summarize by from sum to Average.[/li]
[li]Click OK.[/li]
[li]Click Finish.[/li]
[/ul]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

LOL - and that is how you actually do what I suggested, step by step !!

not sure you need 2 helper columns though - can actually be done without any helper columns using the GROUP feature to Group by Month on the Date field

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

Dang. Under "Add two 'helper columns'" above, the second line should read:

[ul][ul][li]In column D, type [COLOR=blue white]=year(A2)[/color][/li][/ul][/ul]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

also Dang - you would need to group on Year as well so

Dates in ROW and Dates in COLUMN, gouping on YEAR for ROW and on MONTH for COLUMN

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

can actually be done without any helper columns using the GROUP feature to Group by Month on the Date field

Ah, but you can't have the same field (Date) as both column and row, only one or the other.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

ooooh - never noticed that before - cheers for the heads up John.

Helper columns it is then !!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[virtualranger]

Many thanks for replies, I was indeed just looking at pivot tables as the solution.

Thanks,
Jamie