# Average, Large, Date Range question

#### Bigtime

##### New Member
Hello,

I am calculating a weekly score for number of steps performed. I'm taking the top 5 days out of the 7 days in a week using this:

=AVERAGE(LARGE(\$B\$2:\$B\$8,{1,2,3,4,5}))

The NO HOLIDAYS table in my spreadsheet is working perfectly. The HOLIDAYS table is just a copy of that table which I'll work from to try and incorporate the Holiday dates.

If any day during a week falls on a holiday, I want to assign MaxSteps for any holiday day, which is 10000 in my spreadsheet.

One clue I have about going about this would be to somehow incorporate =IF(AND(H2>=(\$D\$28),H2<=(\$E\$28)),"10000","") to assign MaxSteps for a day if it's a holiday.

Other than that, I'm stuck in trying to figure out how to generate my Holidays table of scores.

Any help would be much appreciated!

ABCDEFGHIJKLMNOPQRST
1idStepsLAMFAMSAMVM
210107320002015877529BD681/25/2021NO HOLIDAYS
410107520001225790529BD681/27/2021Week 1140002000.0050.0020505010000100
510110220001865861529BD681/28/2021Week 2162382447.6050.0024.4765050
610109320002135917529BD681/29/2021Week 3437007199.00197.0071.99100100
710109420001285867529BD681/30/2021
810109520001475974529BD681/31/2021
910110320002265675529BD682/1/2021
1010110420002065892529BD682/2/2021`
1110113620002045677529BD682/3/2021
1210113720001795909529BD682/4/2021
1310113820009051269529BD682/5/202110000
1410113920001615833529BD682/6/2021
1510114142381430855029BD682/7/2021
1610116041341786918029BD682/8/2021
17101161836824947592529BD682/9/2021
18101162559521427876129BD682/10/2021
19101180686721620823029BD682/11/2021
201011817447161357672129BD682/12/2021
211011827718215257291629BD682/13/2021
2210118335719001350029BD682/14/2021
23
24
25HOLIDAYS
27StartEndWeek 1140002000.0050.00205050
28Birthday1/25/20212/2/2021Week 2162382447.6050.0024.4765050
29Christmas2/5/20212/7/2021Week 3437007199.00197.0071.99100100
activities (1)
Cell Formulas
RangeFormula
K4,K27K4=SUM(\$B\$2:\$B\$8)
L4,L27L4=AVERAGE(LARGE(\$B\$2:\$B\$8,{1,2,3,4,5}))
M4,M27M4=SUM(LARGE(\$D\$2:\$D\$8,{1,2,3,4,5})+ LARGE(\$F\$2:\$F\$8,{1,2,3,4,5}))
N4,N27N4=MIN(100,(\$L\$4/\$R\$4)*100)
O4,O27O4=MIN(100,(\$M\$4/\$S\$4)*100)
P4,P27P4=MAX(\$N\$4:\$O\$4)
K5,K28K5=SUM(\$B\$9:\$B\$15)
L5,L28L5=AVERAGE(LARGE(\$B\$9:\$B\$15,{1,2,3,4,5}))
M5,M28M5=SUM(LARGE(\$D\$9:\$D\$15,{1,2,3,4,5})+ LARGE(\$F\$9:\$F\$15,{1,2,3,4,5}))
N5,N28N5=MIN(100,(\$L\$5/\$R\$4)*100)
O5,O28O5=MIN(100,(\$M\$5/\$S\$4)*100)
P5,P28P5=MAX(\$N\$5:\$O\$5)
K6,K29K6=SUM(\$B\$16:\$B\$22)
L6,L29L6=AVERAGE(LARGE(\$B\$16:\$B\$22,{1,2,3,4,5}))
M6,M29M6=SUM(LARGE(\$D\$16:\$D\$22,{1,2,3,4,5})+ LARGE(\$F\$16:\$F\$22,{1,2,3,4,5}))
N6,N29N6=MIN(100,(\$L\$6/\$R\$4)*100)
O6,O29O6=MIN(100,(\$M\$6/\$S\$4)*100)
P6,P29P6=MAX(\$N\$6:\$O\$6)
K13K13=IF(AND(H2>=(\$D\$28),H2<=(\$E\$28)),"10000","")

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Replies
5
Views
100
Replies
1
Views
126
Replies
1
Views
66
Replies
7
Views
111
Replies
5
Views
394

1,129,840
Messages
5,638,656
Members
417,041
Latest member
Molo

### 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.

### Which adblocker are you using?

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

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