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!
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 | ||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||
| 1 | id | Steps | LAM | FAM | SAM | VM | ||||||||||||||||
| 2 | 101073 | 2000 | 201 | 5 | 877 | 5 | 29BD68 | 1/25/2021 | NO HOLIDAYS | |||||||||||||
| 3 | 101074 | 2000 | 187 | 5 | 768 | 5 | 29BD68 | 1/26/2021 | Total Steps | Average Steps | Active Minutes | Steps Grade | Minutes Grade | Final | MaxSteps | MaxMinutes | ||||||
| 4 | 101075 | 2000 | 122 | 5 | 790 | 5 | 29BD68 | 1/27/2021 | Week 1 | 14000 | 2000.00 | 50.00 | 20 | 50 | 50 | 10000 | 100 | |||||
| 5 | 101102 | 2000 | 186 | 5 | 861 | 5 | 29BD68 | 1/28/2021 | Week 2 | 16238 | 2447.60 | 50.00 | 24.476 | 50 | 50 | |||||||
| 6 | 101093 | 2000 | 213 | 5 | 917 | 5 | 29BD68 | 1/29/2021 | Week 3 | 43700 | 7199.00 | 197.00 | 71.99 | 100 | 100 | |||||||
| 7 | 101094 | 2000 | 128 | 5 | 867 | 5 | 29BD68 | 1/30/2021 | ||||||||||||||
| 8 | 101095 | 2000 | 147 | 5 | 974 | 5 | 29BD68 | 1/31/2021 | ||||||||||||||
| 9 | 101103 | 2000 | 226 | 5 | 675 | 5 | 29BD68 | 2/1/2021 | ||||||||||||||
| 10 | 101104 | 2000 | 206 | 5 | 892 | 5 | 29BD68 | 2/2/2021 | ` | |||||||||||||
| 11 | 101136 | 2000 | 204 | 5 | 677 | 5 | 29BD68 | 2/3/2021 | ||||||||||||||
| 12 | 101137 | 2000 | 179 | 5 | 909 | 5 | 29BD68 | 2/4/2021 | ||||||||||||||
| 13 | 101138 | 2000 | 90 | 5 | 1269 | 5 | 29BD68 | 2/5/2021 | 10000 | |||||||||||||
| 14 | 101139 | 2000 | 161 | 5 | 833 | 5 | 29BD68 | 2/6/2021 | ||||||||||||||
| 15 | 101141 | 4238 | 143 | 0 | 855 | 0 | 29BD68 | 2/7/2021 | ||||||||||||||
| 16 | 101160 | 4134 | 178 | 6 | 918 | 0 | 29BD68 | 2/8/2021 | ||||||||||||||
| 17 | 101161 | 8368 | 249 | 47 | 592 | 5 | 29BD68 | 2/9/2021 | ||||||||||||||
| 18 | 101162 | 5595 | 214 | 27 | 876 | 1 | 29BD68 | 2/10/2021 | ||||||||||||||
| 19 | 101180 | 6867 | 216 | 20 | 823 | 0 | 29BD68 | 2/11/2021 | ||||||||||||||
| 20 | 101181 | 7447 | 161 | 35 | 767 | 21 | 29BD68 | 2/12/2021 | ||||||||||||||
| 21 | 101182 | 7718 | 215 | 25 | 729 | 16 | 29BD68 | 2/13/2021 | ||||||||||||||
| 22 | 101183 | 3571 | 90 | 0 | 1350 | 0 | 29BD68 | 2/14/2021 | ||||||||||||||
| 23 | ||||||||||||||||||||||
| 24 | ||||||||||||||||||||||
| 25 | HOLIDAYS | |||||||||||||||||||||
| 26 | Holidays | Total Steps | Average Steps | Active Minutes | Steps Grade | Minutes Grade | Final | |||||||||||||||
| 27 | Start | End | Week 1 | 14000 | 2000.00 | 50.00 | 20 | 50 | 50 | |||||||||||||
| 28 | Birthday | 1/25/2021 | 2/2/2021 | Week 2 | 16238 | 2447.60 | 50.00 | 24.476 | 50 | 50 | ||||||||||||
| 29 | Christmas | 2/5/2021 | 2/7/2021 | Week 3 | 43700 | 7199.00 | 197.00 | 71.99 | 100 | 100 | ||||||||||||
activities (1) | ||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| K4,K27 | K4 | =SUM($B$2:$B$8) |
| L4,L27 | L4 | =AVERAGE(LARGE($B$2:$B$8,{1,2,3,4,5})) |
| M4,M27 | M4 | =SUM(LARGE($D$2:$D$8,{1,2,3,4,5})+ LARGE($F$2:$F$8,{1,2,3,4,5})) |
| N4,N27 | N4 | =MIN(100,($L$4/$R$4)*100) |
| O4,O27 | O4 | =MIN(100,($M$4/$S$4)*100) |
| P4,P27 | P4 | =MAX($N$4:$O$4) |
| K5,K28 | K5 | =SUM($B$9:$B$15) |
| L5,L28 | L5 | =AVERAGE(LARGE($B$9:$B$15,{1,2,3,4,5})) |
| M5,M28 | M5 | =SUM(LARGE($D$9:$D$15,{1,2,3,4,5})+ LARGE($F$9:$F$15,{1,2,3,4,5})) |
| N5,N28 | N5 | =MIN(100,($L$5/$R$4)*100) |
| O5,O28 | O5 | =MIN(100,($M$5/$S$4)*100) |
| P5,P28 | P5 | =MAX($N$5:$O$5) |
| K6,K29 | K6 | =SUM($B$16:$B$22) |
| L6,L29 | L6 | =AVERAGE(LARGE($B$16:$B$22,{1,2,3,4,5})) |
| M6,M29 | M6 | =SUM(LARGE($D$16:$D$22,{1,2,3,4,5})+ LARGE($F$16:$F$22,{1,2,3,4,5})) |
| N6,N29 | N6 | =MIN(100,($L$6/$R$4)*100) |
| O6,O29 | O6 | =MIN(100,($M$6/$S$4)*100) |
| P6,P29 | P6 | =MAX($N$6:$O$6) |
| K13 | K13 | =IF(AND(H2>=($D$28),H2<=($E$28)),"10000","") |
