Weekday Averages

[Injinia]

Hi,


I have a range of data with with the first column(column A) being Days of the week ie Su-Sa. I would like to get the averages of the other INDIVIDUAL columns(not all of them collectively) of data if the week's day is only between Monday-Friday.


Cheers

Injinia

 

[Faseeh]

Hi Injinia,


Assuming your data is present like this in A1:B10....

[pre]

Saturday, September 01, 2012	40
Sunday, September 02, 2012	10
Monday, September 03, 2012	20
Tuesday, September 04, 2012	20
Wednesday, September 05, 2012	20
Thursday, September 06, 2012	10
Friday, September 07, 2012	20
Saturday, September 08, 2012	20
Sunday, September 09, 2012	10
Monday, September 10, 2012	40[/pre]

You can use:

=SUMPRODUCT((WEEKDAY($A$1:$A$10)>=2)*(WEEKDAY($A$1:$A$10)<=6)*($B$1:$B$10))/SUMPRODUCT((WEEKDAY($A$1:$A$10)>=2)*(WEEKDAY($A$1:$A$10)<=6)*1)

Regards,

 

[Injinia]

Hi,


Thanks Faseeh,


I have the days and dates in separate columns, so days Monday - Sunday are in their own column and not "Mon, September 10 2012". I tried to use the same formula to no avail...

Should I have something different?


-Injinia

 

[Faseeh]

With Days in Col. A and data to be summed in col B you can use:

SUMPRODUCT(((A1:A10)<>"Saturday")*(A1:A10<>"Sunday")*(B1:B10))/SUMPRODUCT(((A1:A10)<>"Saturday")*(A1:A10<>"Sunday")*1)

Let me know otherwise will upload a sample file!


Regards,


Faseeh

 

[shrivallabha]

If your dates are true dates (and in column B) then Faseeh's approach should've worked.

See if this works:

=SUMPRODUCT(--(WEEKDAY(B1:B10,2)<6),C1:C10)/SUMPRODUCT(--(WEEKDAY(B1:B10,2)<6))

 

[SirJB7]

@Faseeh

Hi!

As I wrote here:

http://chandoo.org/forums/topic/how-to-highlight-sundays#post-42028

it's advisable to take shrivallabha path of numbers instead of strings regarding issues about language versions other than english.

Regards!

 

[Faseeh]

Hi SirJB7,


The formula in my second last post follow Shri's approach & uses Weekday(), but Injina is looking for something different (read her last post), however Shri has optimally used weekday() unlike one used by me.


Regards,