Average, Large, Date Range question

Bigtime

New Member
Joined
Feb 11, 2014
Messages
26
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!

Grade Calculation.xlsx
ABCDEFGHIJKLMNOPQRST
1idStepsLAMFAMSAMVM
210107320002015877529BD681/25/2021NO HOLIDAYS
310107420001875768529BD681/26/2021Total StepsAverage StepsActive MinutesSteps GradeMinutes GradeFinalMaxStepsMaxMinutes
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
26HolidaysTotal StepsAverage StepsActive MinutesSteps GradeMinutes GradeFinal
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"

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top