day of Month

[notaclue]

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

 

[Mike H]

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

 

[Gary''s Student]

=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.

 

[notaclue]

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?

 

[Mike H]

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

 

[notaclue]

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

 

[Mike H]

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

 

[notaclue]

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

 

[John5835]

--
thanks,
John