Daily and Monthly Average Macro

Hello,
I need some help to make a macro that look up for values on a specific day
and month to make an average. Here I show how it looks

A B C
Day Hour Value
01/01/05 8:00 10
01/01/05 9:00 11
01/01/05 10:00 20

02/01/05 8:00 25
03/01/05 8:00 30

The macro should be able to make an average of the day entered (01/01/05) by
the user and for a second option doing it with the moth.

If the day entered by the user is in the column A I need to store the value
and go on to the second line to do the same, storing the value to get the
average of them as an output

Same with the monthly average but searching the /mm/ space

Any help is welcome Im just need a hand to start

Have you considered using a pivot table and grouping ont the time field. It
avoids a whole pile of messy code and gives you a lot more options... If you
need help just ask...
--
HTH...

Jim Thomlinson


"snake" wrote:

> Hello,
> I need some help to make a macro that look up for values on a specific day
> and month to make an average. Here I show how it looks
>
> A B C
> Day Hour Value
> 01/01/05 8:00 10
> 01/01/05 9:00 11
> 01/01/05 10:00 20
>
> 02/01/05 8:00 25
> 03/01/05 8:00 30
>
> The macro should be able to make an average of the day entered (01/01/05) by
> the user and for a second option doing it with the moth.
>
> If the day entered by the user is in the column A I need to store the value
> and go on to the second line to do the same, storing the value to get the
> average of them as an output
>
> Same with the monthly average but searching the /mm/ space
>
> Any help is welcome Im just need a hand to start

Why use a macro? Put the test date in D1 and use

=AVERAGE(IF((YEAR(A2:A100)=YEAR(D1))*(MONTH(A2:A100)=MONTH(D1))*(DAY(A2:A100
)=DAY(D1)),C2:C100))

and

=AVERAGE(IF((YEAR(A2:A100)=YEAR(D1))*(MONTH(A2:A100)=MONTH(D1)),C2:C100))

both array formulae so commit with Ctrl-Shift-Enter.



--
HTH

Bob Phillips

"snake" <snake@discussions.microsoft.com> wrote in message
news:363FFC07-DACE-4718-A033-D0FDC1F8CD6B@microsoft.com...
> Hello,
> I need some help to make a macro that look up for values on a specific day
> and month to make an average. Here I show how it looks
>
> A B C
> ?>
> The macro should be able to make an average of the day entered (01/01/05)
by
> the user and for a second option doing it with the moth.
>
> If the day entered by the user is in the column A I need to store the
value
> and go on to the second line to do the same, storing the value to get the
> average of them as an output
>
> Same with the monthly average but searching the /mm/ space
>
> Any help is welcome Im just need a hand to start

Great simple solution, thank you very much

"Bob Phillips" wrote:

> Why use a macro? Put the test date in D1 and use
>
> =AVERAGE(IF((YEAR(A2:A100)=YEAR(D1))*(MONTH(A2:A100)=MONTH(D1))*(DAY(A2:A100
> )=DAY(D1)),C2:C100))
>
> and
>
> =AVERAGE(IF((YEAR(A2:A100)=YEAR(D1))*(MONTH(A2:A100)=MONTH(D1)),C2:C100))
>
> both array formulae so commit with Ctrl-Shift-Enter.
>
>
>
> --
> HTH
>
> Bob Phillips
>
> "snake" <snake@discussions.microsoft.com> wrote in message
> news:363FFC07-DACE-4718-A033-D0FDC1F8CD6B@microsoft.com...
> > Hello,
> > I need some help to make a macro that look up for values on a specific day
> > and month to make an average. Here I show how it looks
> >
> > A B C
> > ?>
> > The macro should be able to make an average of the day entered (01/01/05)
> by
> > the user and for a second option doing it with the moth.
> >
> > If the day entered by the user is in the column A I need to store the
> value
> > and go on to the second line to do the same, storing the value to get the
> > average of them as an output
> >
> > Same with the monthly average but searching the /mm/ space
> >
> > Any help is welcome Im just need a hand to start
>
>
>