asked on

Monthly average - VBA

Hi,
I have came up with my own solution for this but I am not if its the neatest approach to do it.

all I am trying to do is to get the monthly average of the price for the dates and populate against the cells. (in VBA)
Please see the attahed file.
It would be great if some one provide me with code that would populate the 'Monthly Avg' - Column C with the price average for that month.

Many thanks
monthlyavg.xls

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Hello,

any particular reason for VBA? A formula would be much faster. Start in C2

=AVERAGE(IF(MONTH($A$2:$A$24)=MONTH(A2),$B$2:$B$24,""))

Confirm with Ctrl-Shift-Enter and copy down.

cheers, teylyn

ASKER CERTIFIED SOLUTION

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SameerMirza

ASKER

Thanks but in this case the records would go beyond one year
That is multiple years.
so we would have to add check for multiple years too.
I tried,

=AVERAGE(IF(AND(YEAR($A$2:$A$24)=YEAR(A2), MONTH($A$2:$A$24)=MONTH(A2)),$B$2:$B$24,""))

But it fails. Because its trying to locate the same value in the whole range.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

AND() does not work in this context. You can use this instead:

=AVERAGE(IF((YEAR($A$2:$A$24)=YEAR(A2))*(MONTH($A$2:$A$24)=MONTH(A2)),$B$2:$B$24,""))

Remember to confirm with Ctrl-Shift-Enter

cheers, teylyn

SameerMirza

ASKER

Thanks. That seems to work fine
Although I have written a module too (which works :)) using loops but I think your would be much quicker.

I have another very quick one.
I am trying to freeze pans.
Go select row -> window -> freeze pane -
for some reason it is freezing on 16 rows

tried doing it from VBA too. Get the same result
'ActiveWindow.FreezePanes = True'

How can I do it from VBA such that it would freeze on header/first row?

Thanks

SameerMirza

ASKER

Please dont bother reading what i just asked. :)
you are suppose to select the data and I was selecting the first row :/