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

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

"notaclue" wrote:

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

=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.
--
Gary''s Student - gsnu200789


"notaclue" wrote:

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

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" wrote:

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

"notaclue" wrote:

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

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" wrote:

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" wrote:

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

"notaclue" wrote:

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

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" wrote:

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" wrote:

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" wrote:

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

"notaclue" wrote:

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

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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

"notaclue" wrote:

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

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

"Mike H" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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

"notaclue" wrote:

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

--
thanks,
John


"notaclue" wrote:

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