day of Month

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

notaclue - 31 May 2008 10:29 GMT

Hi,
I#m looking for a Formula that returns me the Value of a specific day of a
month.
Example:
Colum A Dates Colum B Values. Now I need the Average Value for each day of
the month.
Now I want in a new sheet the folowing:
Average Value of 1st of all Month
Average Value of 2nd of all Month
Average Value of 3rd of all Month
etc.
Sure I need the Formula for each of that, My main Problem how to "Filter"
all the 1st, 2nd etc.

Thanks so long

Reply to this Message

Mike H - 31 May 2008 11:44 GMT

Hi,

You don't need to filter, put this in a cell.

=AVERAGE(IF(DAY(A1:A100)=ROW(A1),B1:B100,FALSE))

Array entered with
Ctrl+Shift+Enter
then drag down 31 rows for days 1 - 31

Mike

> Hi,
> I#m looking for a Formula that returns me the Value of a specific day of a
[quoted text clipped - 11 lines]
>
> Thanks so long

Reply to this Message

notaclue - 31 May 2008 13:05 GMT

Hi,
Thanks a lot for your help guys!!
got a solution by myself in the meantime

=AVERAGE(IF((DAY('High low'!C6:C245)=17)*('High low'!C6:C245<>0),'High
low'!B6:B245))

BUT:
I discovered that I need the workdays otherwise it's pretty useless to me

so the next question how do I exclude the Weekends or better how do i get
the 1st, 2nd, 3rd etc workday of the Month?

> Hi,
>
[quoted text clipped - 23 lines]
> >
> > Thanks so long

Reply to this Message

Mike H - 31 May 2008 14:42 GMT

Hi,

I altered you ranges to suit the test data I had set up. This now only
averages Mon - Fri

=AVERAGE(IF((DAY('High Low'!A1:A100)=1)*(WEEKDAY('High
Low'!A1:A100,2)<6)*('High Low'!B1:B100 > 0),'High Low'!B1:B100))

Array entered

Mike

> Hi,
> Thanks a lot for your help guys!!
[quoted text clipped - 36 lines]
> > >
> > > Thanks so long

Reply to this Message

notaclue - 31 May 2008 15:13 GMT

Hi, great it works!
Thanks again
just a question to it the Day range is 31 days , right?
but the Workingday of a month are average 21, rigt?
Do I need to alter the formula now just 21 times (day 1 to 21) or 31 times
day1 to day31). I`m a bid confused what the formula now excaxtly tells me.
To make it clear 1 need the average of 1st working day until the 21st (23rd
at most) working day.
I tried to alter it 31 times (day1 to day 31 of a calender month) but now I
can't find out where are the working day and where aren't.
I hope it is understandable what I mean.
Greatings

> Hi,
>
[quoted text clipped - 48 lines]
> > > >
> > > > Thanks so long

Reply to this Message

Mike H - 31 May 2008 16:04 GMT

You need to check 31 days because sometimes day 31 will be a weekday and be
counted and sometimes a weekend and won't but you don't have to alter the
fromula, simply edit it it like this

=AVERAGE(IF((DAY('High Low'!A1:A100)=Row(a1))*(WEEKDAY('High
Low'!A1:A100,2)<6)*('High Low'!B1:B100 > 0),'High Low'!B1:B100))

note that '1' has been changed to row(a1) which returns a 1 and if you drag
it down will return 2 etc for each day of the month. If you do this then you
must make the ranges absolute with $A$1 etc or they will increment when
dragged.

Mike

> Hi, great it works!
> Thanks again
[quoted text clipped - 61 lines]
> > > > >
> > > > > Thanks so long

Reply to this Message

notaclue - 31 May 2008 16:10 GMT

Hi Mike thanks a lot for your help, great!!
It just doenst resove my problem then I need the Average of every 1st till
23rd Workingday of the month. it is a Statistik of Stocks advancing and
decliningon specific days of the month to creat a seasonal Chart to see at
what time the big money goes in etc.... so if i have a 31 day list i still
don't get where i want to be.
But anyway I might find a solution.

Thanks again for your work

> You need to check 31 days because sometimes day 31 will be a weekday and be
> counted and sometimes a weekend and won't but you don't have to alter the
[quoted text clipped - 75 lines]
> > > > > >
> > > > > > Thanks so long

Reply to this Message

Gary''s Student - 31 May 2008 11:46 GMT

=SUMPRODUCT(--(DAY(A1:A100)=1),B1:B100)
for the first of the month
=SUMPRODUCT(--(DAY(A1:A100)=2),B1:B100) for the second of the month, etc.

Signature

Gary''s Student - gsnu200789

> Hi,
> I#m looking for a Formula that returns me the Value of a specific day of a
[quoted text clipped - 11 lines]
>
> Thanks so long

Reply to this Message

John5835 - 21 Aug 2008 16:39 GMT

Signature

thanks,
John

> Hi,
> I#m looking for a Formula that returns me the Value of a specific day of a
[quoted text clipped - 11 lines]
>
> Thanks so long

Reply to this Message