average by weekdays and sorting?

rasko

New Member
Joined
Mar 24, 2018
Messages
7


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)
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

RileyC

New Member
Joined
Aug 22, 2018
Messages
42
Vba function for averaging, wrote on mobile and didn't include error checking or any complexity but this would work I believe.

Public function averageDay(day as String, days as Range, player as Range) as float
Dim count as int
For i to days.Count
if days.Cells (i,1).value2 = day then
averageDay = player (cells(i,1)).value2 + averageDay
count = count+1
end if
Next i
averageDay = averageDay / count
End function
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,787
Office Version
365
Platform
Windows
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)
I'm thinking those 'blank' cells are not actually blank but contain a formula returning ""?
Try adding an extra check ..
=AVERAGE(IF($V$4:$V$34<>"";IF(WEEKDAY($V$4:$V$34)=6;($X$4:$X$34))))

If you need further help, please post your sample data in a form we can copy/paste to test, not an image. My signature block below has a link with suggestions.
 
Last edited:

rasko

New Member
Joined
Mar 24, 2018
Messages
7
thank you sir, many thanks to both of you...

as you noticed, there are some formulas in blank cells,returning ""
but your "=AVERAGE(IF($V$4:$V$34<>"";IF(WEEKDAY($V$4:$V$34)=6;($X$4:$X$34))))" solved my problem.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,787
Office Version
365
Platform
Windows
You're welcome. Thanks for the follow up. :)
 

rasko

New Member
Joined
Mar 24, 2018
Messages
7
sorry, i couldn'd make a data form, but my table formulas; as below;

 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,787
Office Version
365
Platform
Windows
sorry, i couldn'd make a data form, but my table formulas; as below;
Is there a further question here or are you just showing us that everything is okay?
 

rasko

New Member
Joined
Mar 24, 2018
Messages
7
UVWXYZAA
3monthTOTAL GUESTS INDaysAverage
4septembar 201801Saturday38Monday25,0
502Sunday28Tuesday24,8
603Monday42Wednesday18,0
704Tuesday59Thursday17,3
805Wednesday48Friday15,3
906Thursday34Saturday26,2
1007Friday24Sunday21,0
1108Saturday35Average39,8
1209Sunday37
1310Monday58DaysAverage
1411Tuesday40Saturday26,2
1512Wednesday24Monday25,0
1613Thursday35Tuesday24,8
1714Friday37Sunday21,0
1815Saturday58Wednesday18,0
1916Sunday40Thursday17,3
2017MondayFriday15,3
2118TuesdayAverage39,8
2219Wednesday
2320Thursday
2421Friday
2522Saturday
2623Sunday
2724Monday
2825Tuesday
2926Wednesday
3027Thursday
3128Friday
3229Saturday
3330Sunday
34

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
sep 2018

Worksheet Formulas
CellFormula
V4=U4
W4=TEXT(V4,"[$-409]GGGG")
V5=V4+1
W5=TEXT(V5,"[$-409]GGGG")
V6=V5+1
W6=TEXT(V6,"[$-409]GGGG")
V7=V6+1
W7=TEXT(V7,"[$-409]GGGG")
V8=V7+1
W8=TEXT(V8,"[$-409]GGGG")
V9=V8+1
W9=TEXT(V9,"[$-409]GGGG")
V10=V9+1
W10=TEXT(V10,"[$-409]GGGG")
V11=V10+1
W11=TEXT(V11,"[$-409]GGGG")
V12=V11+1
W12=TEXT(V12,"[$-409]GGGG")
V13=V12+1
W13=TEXT(V13,"[$-409]GGGG")
V14=V13+1
W14=TEXT(V14,"[$-409]GGGG")
V15=V14+1
W15=TEXT(V15,"[$-409]GGGG")
V16=V15+1
W16=TEXT(V16,"[$-409]GGGG")
V17=V16+1
W17=TEXT(V17,"[$-409]GGGG")
V18=V17+1
W18=TEXT(V18,"[$-409]GGGG")
V19=V18+1
W19=TEXT(V19,"[$-409]GGGG")
V20=V19+1
W20=TEXT(V20,"[$-409]GGGG")
V21=V20+1
W21=TEXT(V21,"[$-409]GGGG")
V22=V21+1
W22=TEXT(V22,"[$-409]GGGG")
V23=V22+1
W23=TEXT(V23,"[$-409]GGGG")
V24=V23+1
W24=TEXT(V24,"[$-409]GGGG")
V25=V24+1
W25=TEXT(V25,"[$-409]GGGG")
V26=V25+1
W26=TEXT(V26,"[$-409]GGGG")
V27=V26+1
W27=TEXT(V27,"[$-409]GGGG")
V28=V27+1
W28=TEXT(V28,"[$-409]GGGG")
V29=V28+1
W29=TEXT(V29,"[$-409]GGGG")
V30=V29+1
W30=TEXT(V30,"[$-409]GGGG")
V31=IF(EOMONTH($B$4,0)<(V30+1),"",(V30+1))
W31=TEXT(V31,"[$-409]GGGG")
V32=IF(EOMONTH($B$4,0)<(V30+2),"",(V30+2))
W32=TEXT(V32,"[$-409]GGGG")
V33=IF(EOMONTH($B$4,0)<(V30+3),"",(V30+3))
W33=TEXT(V33,"[$-409]GGGG")
V34=IF(EOMONTH($B$4,0)<(V30+4),"",(V30+4))
W34=TEXT(V34,"[$-409]GGGG")
AA11=AVERAGE(X4:X34)
Z14=INDEX($Z$4:$Z$10,MATCH(AA14,$AA$4:$AA$10,0))
AA14=LARGE($AA$4:$AA$10,1)
Z15=INDEX($Z$4:$Z$10,MATCH(AA15,$AA$4:$AA$10,0))
AA15=LARGE($AA$4:$AA$10,2)
Z16=INDEX($Z$4:$Z$10,MATCH(AA16,$AA$4:$AA$10,0))
AA16=LARGE($AA$4:$AA$10,3)
Z17=INDEX($Z$4:$Z$10,MATCH(AA17,$AA$4:$AA$10,0))
AA17=LARGE($AA$4:$AA$10,4)
Z18=INDEX($Z$4:$Z$10,MATCH(AA18,$AA$4:$AA$10,0))
AA18=LARGE($AA$4:$AA$10,5)
Z19=INDEX($Z$4:$Z$10,MATCH(AA19,$AA$4:$AA$10,0))
AA19=LARGE($AA$4:$AA$10,6)
Z20=INDEX($Z$4:$Z$10,MATCH(AA20,$AA$4:$AA$10,0))
AA20=LARGE($AA$4:$AA$10,7)
AA21=AVERAGE(X4:X34)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
AA4{=AVERAGE(IF($V$4:$V$34<>"",IF(WEEKDAY($V$4:$V$34)=2,($X$4:$X$34))))}
AA5{=AVERAGE(IF($V$4:$V$34<>"",IF(WEEKDAY($V$4:$V$34)=3,($X$4:$X$34))))}
AA6{=AVERAGE(IF($V$4:$V$34<>"",IF(WEEKDAY($V$4:$V$34)=4,($X$4:$X$34))))}
AA7{=AVERAGE(IF($V$4:$V$34<>"",IF(WEEKDAY($V$4:$V$34)=5,($X$4:$X$34))))}
AA8{=AVERAGE(IF($V$4:$V$34<>"",IF(WEEKDAY($V$4:$V$34)=6,($X$4:$X$34))))}
AA9{=AVERAGE(IF($V$4:$V$34<>"",IF(WEEKDAY($V$4:$V$34)=7,($X$4:$X$34))))}
AA10{=AVERAGE(IF($V$4:$V$34<>"",IF(WEEKDAY($V$4:$V$34)=1,($X$4:$X$34))))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 

rasko

New Member
Joined
Mar 24, 2018
Messages
7
sorry for the inconvenience ;

i have got a additional question, for example; only two value entered for monday, but average is 25
it considers all mondays in month, before i entered any value, how can i solve that?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,787
Office Version
365
Platform
Windows
I can't follow.

When posting sample data and formulas, just post the formula for the top row of each column or section. If the post gets long it is hard to navigate & follow easily.

What, exactly is in U4?
- Text?
- date (number) formatted as "mmmm yyyy"? (what date?)
- formula (what formula?)

What, exactly, if anything is in U5, U6, etc?
 

Watch MrExcel Video

Forum statistics

Threads
1,100,214
Messages
5,473,202
Members
406,851
Latest member
Debbren

This Week's Hot Topics

Top