average by weekdays and sorting?

[rasko]

hi,
i have a daily total players list, i wanted to make a list and chart, which has number of average players by weekdays (rightside of the picture) in descending order. i used AVERAGE(IF(WEEKDAY($V$4:$V$34)=6;($X$4:$X$34))) and ctrl shift+enter ; but is give a ˝value˝ error, when any cell blank in ($V$4:$V$34);
and i am using 2 small list for get data; the first one, calculate averages by weekdays, second is sorting them with ˝large˝ formula and for to get which day is the best ˝index, match˝ formula...
is it possible in single small list calculate averages(ignores blanks and zeros) and sort them?
(i don´t want to use pivot table)

 

[rasko]

yes sir, you are right,

cell u4 : date (number) formatted as "mmmm yyyy" ; than i could change month with ¨data valitadion¨
column v : date series with formatted numbers,taken start date from u4; if less than 31 days( exp february 28days); it leaves blank with ¸¨IF(EOMONTH($u$4,0)<(V30+1),"",(V30+1))¨
column w: weekdays from column v ¸¨ TEXT(V28,"[$-409]dddd")¨
column x: values, written manually (daily costumers)
column z and aa: day names and average of values by day
second table column aa: taking values from first small table and sorting values with ¨LARGE($AA$4:$AA$10,1)¨
second table column z : determines weekdays in descending order with ¨INDEX($Z$4:$Z$10,MATCH(AA15,$AA$4:$AA$10,0))¨
there is nothing in cell u5,u6,...

q1: is it possible, in single table in descending order ; averages and weekdays, instead of two small tables?
q2: when i entered the values on column x, for exp 13th of the month,i have only 2 values for monday, but our formula divide 4 for averages ¨{=AVERAGE(IF($V$4:$V$34<>"",IF(WEEKDAY($V$4:$V$34)=2,($X$4:$X$34))))}¨

i am sure so more practical ways for that table but i am a rookie for excel, i hope i explained now

 

[Peter_SSs]

q1: is it possible, in single table in descending order ; averages and weekdays, instead of two small tables?

That would be possible but I think the formulas would be very large. I think I would stick to the two tables. If you don't like the look of the 2 tables, you could move the top one out to the right and hide those columns.

q2: when i entered the values on column x, for exp 13th of the month,i have only 2 values for monday, but our formula divide 4 for averages

OK, I have addressed that below, but also note that I have changed your formula for column Z in the bottom table. You need that change otherwise if it ever happens that two (or more) days in the top table have an equal average your existing formula would repeat one day and miss out one day in the bottom table.

Try these.

Excel Workbook
	U	V	W	X	Y	Z	AA
4	1/09/2018	1/09/2018	Saturday	38		Monday	50
5		2/09/2018	Sunday	28		Tuesday	49.5
6		3/09/2018	Monday	42		Wednesday	36
7		4/09/2018	Tuesday	59		Thursday	34.5
8		5/09/2018	Wednesday	48		Friday	30.5
9		6/09/2018	Thursday	34		Saturday	43.66666667
10		7/09/2018	Friday	24		Sunday	35
11		8/09/2018	Saturday	35		Average	39.8125
12		9/09/2018	Sunday	37
13		10/09/2018	Monday	58
14		11/09/2018	Tuesday	40		Monday	50
15		12/09/2018	Wednesday	24		Tuesday	49.5
16		13/09/2018	Thursday	35		Saturday	43.66666667
17		14/09/2018	Friday	37		Wednesday	36
18		15/09/2018	Saturday	58		Sunday	35
19		16/09/2018	Sunday	40		Thursday	34.5
20		17/09/2018	Monday			Friday	30.5
21		18/09/2018	Tuesday			Average	39.8125
22		19/09/2018	Wednesday
23		20/09/2018	Thursday
24		21/09/2018	Friday
25		22/09/2018	Saturday
26		23/09/2018	Sunday
27		24/09/2018	Monday
28		25/09/2018	Tuesday
29		26/09/2018	Wednesday
30		27/09/2018	Thursday
31		28/09/2018	Friday
32		29/09/2018	Saturday
33		30/09/2018	Sunday
34
Av