average by weekdays and sorting?

R

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)
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
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
 
Upvote 0
rasko said:
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)
Click to expand...
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:
Upvote 0
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.
 
Upvote 0
You're welcome. Thanks for the follow up. :)
 
Upvote 0
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>
 
Upvote 0
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Auto Sort for Leaderboard
Replies
0
Views
57
NTLKnight
N
N
  • Solved
COUNTIF With Multiple Criteria From Different Ranges
Replies
3
Views
86
Alex Blakenburg
A
S
  • Solved
Formula to calculate average and not include blanks or zeros in the calculation
Replies
4
Views
249
etaf
etaf
M
  • Solved
WEEKDAY Calc to exclude empty cells
Replies
5
Views
650
Fluff
Fluff
L
  • Question
How to calculate averages of specific cells that ignore 0
Replies
3
Views
297
Joe4
Joe4

Forum statistics

Threads
1,214,391
Messages
6,119,249
Members
448,879
Latest member
oksanana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top