Spreadsheet: 817091d1675986016-creating-a-rolling-average-by-using-offset-test-updated-with-suggestions
Sheet: Daily Averages by Location

 | DAILY SALES INFORMATION |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ROLLING 6 WEEK AVERAGE
 |  |  | DR1 |  |  |  | DR2 |  |  |  | RETAIL |  | DR3 |  |  | DR1 |  |  |  | DR2 |  |  |  | DR3
 |  |  | LUNCH |  | DINNER |  | LUNCH |  | DINNER |  |  |  | Lunch | Dinner |  | LUNCH |  | DINNER |  | LUNCH |  | DINNER |  | Lunch | Dinner
 | Week |  | Covers | Ave $ | Covers | Ave $ | Covers | Ave $ | Covers | Ave $ | Tasters | T/A Sales $ |  |  |  | Covers | Ave $ | Covers | Ave $ | Covers | Ave $ | Covers | Ave $
 | 23 | 2022-11-28 00:00:00 | 0 | 0 | 0 | 0 | 61 | 99.00147540983606 | 0 | 0 |  |  |  |  | MONDAY
 | 23 | 2022-11-29 00:00:00 | 0 | 0 | 0 | 0 | 53 | 106.7239622641509 | 0 | 0 |  |  |  |  | TUESDAY
 | 23 | 2022-11-30 00:00:00 | 0 | 0 | 0 | 0 | 43 | 108.3762790697675 | 0 | 0 |  |  |  |  | WEDNESDAY
 | 23 | 2022-12-01 00:00:00 | 0 | 0 | 0 | 0 | 47 | 110.5523404255319 | 0 | 0 |  |  |  |  | THURSDAY
 | 23 | 2022-12-02 00:00:00 | 0 | 0 | 0 | 0 | 91 | 143.777032967033 | 0 | 0 |  |  |  |  | FRIDAY
 | 23 | 2022-12-03 00:00:00 | 0 | 0 | 0 | 0 | 118 | 129.7688983050847 | 74 | 133.9502702702703 |  |  |  |  | SATURDAY
 | 23 | 2022-12-04 00:00:00 | 0 | 0 | 0 | 0 | 143 | 124.8686713286713 | 32 | 97.2303125 |  |  |  |  | SUNDAY
 |  | 2022-12-05 00:00:00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  | 2022-12-06 00:00:00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  | 2022-12-07 00:00:00 | 0 | 0 | 0 | 0 | 53 | 125.9347169811321 | 0 | 0
 |  | 2022-12-08 00:00:00 | 0 | 0 | 0 | 0 | 63 | 142.2726984126984 | 0 | 0
 |  | 2022-12-09 00:00:00 | 0 | 0 | 0 | 0 | 0 | 0 | 58 | 168.6144827586207 |  |  |  | kvsrinivasamurthy |  |  |  | returns Monday, but how do I make it roll forward 6 weeks
 |  | 2022-12-10 00:00:00 | 0 | 0 | 0 | 0 | 119 | 150.1565546218488 | 104 | 154.0017307692308
 |  | 2022-12-11 00:00:00 | 0 | 0 | 0 | 0 | 118 | 145.5300847457627 | 82 | 137.4889024390244 |  |  |  | [NAME] |  |  |  | has a different value? Doesn't return the same value of Mondays I have in this formula
 |  | 2022-12-12 00:00:00 | 0 | 0 | 0 | 0 | 98 | 137.1768367346939 | 0 | 0 |  |  |  |  |  |  |  | how do I speciffy the 6 week range?
 |  | 2022-12-13 00:00:00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |  |  |  | Gregb11 |  |  |  | doesn’t return Monday only is returning an average of whole 6 week period?
 |  | 2022-12-14 00:00:00 | 0 | 0 | 0 | 0 | 88 | 145.5630681818182 | 0 | 0 |  |  |  |  |  |  |  | can you do an IFS with weekday>
 |  | 2022-12-15 00:00:00 | 0 | 0 | 0 | 0 | 92 | 153.1463043478261 | 0 | 0
 |  | 2022-12-16 00:00:00 | 14 | 424.4807142857143 | 28 | 373.7828571428572 | 97 | 132.4085567010309 | 100 | 152.6863
 |  | 2022-12-17 00:00:00 | 18 | 424.7727777777778 | 28 | 295.6492857142857 | 118 | 157.5886440677966 | 97 | 144.6345360824742
 |  | 2022-12-18 00:00:00 | 33 | 402.2039393939394 | 0 | 0 | 124 | 165.3884677419355 | 78 | 139.1257692307692
 |  | 2022-12-19 00:00:00 | 12 | 253.1066666666667 | 0 | 0 | 114 | 137.264649122807 | 0 | 0
 |  | 2022-12-20 00:00:00 | 0 | 0 | 0 | 0 | 113 | 124.1794690265487 | 0 | 0
 |  | 2022-12-21 00:00:00 | 0 | 0 | 0 | 0 | 104 | 148.6451923076923 | 0 | 0
 |  | 2022-12-22 00:00:00 | 0 | 0 | 0 | 0 | 106 | 156.1406603773585 | 0 | 0
 |  | 2022-12-23 00:00:00 | 10 | 409 | 14 | 398.5057142857143 | 87 | 149.0366666666667 | 74 | 153.3906756756757
 |  | 2022-12-24 00:00:00 | 6 | 384.545 | 16 | 450.6825 | 106 | 137.2383962264151 | 104 | 145.0158653846154
 |  | 2022-12-25 00:00:00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  | 2022-12-26 00:00:00 | 18 | 466.0522222222223 | 0 | 0 | 103 | 171.0715533980583 | 0 | 0
 |  | 2022-12-27 00:00:00 | 0 | 0 | 0 | 0 | 119 | 167.4383193277311 | 0 | 0
 |  | 2022-12-28 00:00:00 | 0 | 0 | 0 | 0 | 124 | 147.6883870967742 | 0 | 0
 |  | 2022-12-29 00:00:00 | 0 | 0 | 0 | 0 | 126 | 161.5800793650794 | 0 | 0
 |  | 2022-12-30 00:00:00 | 39 | 443.7879487179487 | 36 | 339.2425 | 121 | 153.7565289256198 | 92 | 153.4040217391305
 |  | 2022-12-31 00:00:00 | 26 | 357.993076923077 | 37 | 372.2481081081082 | 124 | 152.9442741935484 | 117 | 164.195811965812
 |  | 2023-01-01 00:00:00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |  |  |  | connect to Jan Dash
 |  | 2023-01-02 00:00:00 | 37 | 483.2121621621621 | 0 | 0 | 112 | 183.1919642857143 | 0 | 0
 |  | 2023-01-03 00:00:00 | 0 | 0 | 0 | 0 | 128 | 159.873359375 | 0 | 0
 |  | 2023-01-04 00:00:00 | 0 | 0 | 0 | 0 | 128 | 152.98625 | 0 | 0
 |  | 2023-01-05 00:00:00 | 0 | 0 | 0 | 0 | 122 | 157.1422950819672 | 0 | 0
 |  | 2023-01-06 00:00:00 | 35 | 376.4414285714286 | 33 | 420.0827272727273 | 110 | 174.0568181818182 | 90 | 161.1756666666666
 |  | 2023-01-07 00:00:00 | 24 | 319.4316666666666 | 24 | 383.90125 | 117 | 158.3222222222222 | 102 | 152.7380392156863
 |  | 2023-01-08 00:00:00 | 22 | 337.975 | 0 | 0 | 117 | 143.7661538461539 | 94 | 141.1213829787234
 |  | 2023-01-09 00:00:00 | 21 | 381.3857142857144 | 0 | 0 | 114 | 147.5677192982456 | 0 | 0
 |  | 2023-01-10 00:00:00 | 0 | 0 | 0 | 0 | 124 | 157.4837903225807 | 0 | 0
 |  | 2023-01-11 00:00:00 | 0 | 0 | 0 | 0 | 125 | 144.4036 | 0 | 0
 |  | 2023-01-12 00:00:00 | 0 | 0 | 0 | 0 | 127 | 155.6155905511811 | 0 | 0
 |  | 2023-01-13 00:00:00 | 11 | 380.8263636363637 | 19 | 364.7126315789474 | 121 | 156.84 | 98 | 157.0129591836735
 |  | 2023-01-14 00:00:00 | 14 | 353.6364285714286 | 16 | 402.726875 | 135 | 157.0336296296296 | 101 | 156.4837623762376
 |  | 2023-01-15 00:00:00 | 20 | 381.7725 | 0 | 0 | 121 | 153.5461983471074 | 105 | 150.6450476190476
 |  | 2023-01-16 00:00:00 | 9 | 412.2222222222222 | 0 | 0 | 147 | 138.7291156462585 | 0 | 0
 |  | 2023-01-17 00:00:00 | 0 | 0 | 0 | 0 | 128 | 161.736484375 | 0 | 0
 |  | 2023-01-18 00:00:00 | 0 | 0 | 0 | 0 | 122 | 150.4062295081967 | 0 | 0
 |  | 2023-01-19 00:00:00 | 0 | 0 | 0 | 0 | 130 | 148.465 | 0 | 0
 |  | 2023-01-20 00:00:00 | 18 | 549.8988888888889 | 10 | 250.545 | 108 | 144.5791666666667 | 80 | 162.572625
 |  | 2023-01-21 00:00:00 | 22 | 345.9918181818181 | 19 | 370.9568421052632 | 131 | 147.969465648855 | 110 | 145.719
 |  | 2023-01-22 00:00:00 | 23 | 361.2652173913044 | 0 | 0 | 135 | 153.2693333333333 | 84 | 154.3075
 |  | 2023-01-23 00:00:00 | 4 | 297.275 | 0 | 0 | 102 | 135.0267647058823 | 0 | 0
 |  | 2023-01-24 00:00:00 | 0 | 0 | 0 | 0 | 69 | 130.1515942028986 | 0 | 0
 |  | 2023-01-25 00:00:00 | 0 | 0 | 0 | 0 | 62 | 133.3872580645161 | 0 | 0
 |  | 2023-01-26 00:00:00 | 0 | 0 | 0 | 0 | 57 | 118.5964912280702 | 0 | 0
 |  | 2023-01-27 00:00:00 | 10 | 384.727 | 17 | 338.4494117647059 | 81 | 140.0001234567901 | 74 | 138.6817567567568
 |  | 2023-01-28 00:00:00 | 24 | 388.53375 | 31 | 390.0293548387097 | 116 | 159.7413793103448 | 100 | 159.1955
 |  | 2023-01-29 00:00:00 | 12 | 273.1058333333333 | 0 | 0 | 115 | 149.606347826087 | 89 | 136.9419101123595
 |  | 2023-01-30 00:00:00 | 4 | 297.275 | 0 | 0 | 102 | 135.0267647058823 | 0 | 0
 |  | 2023-01-31 00:00:00 | 0 | 0 | 0 | 0 | 69 | 130.1515942028986 | 0 | 0
 |  | 2023-02-01 00:00:00 | 0 | 0 | 0 | 0 | 62 | 133.3872580645161 | 0 | 0
 |  | 2023-02-02 00:00:00 | 0 | 0 | 0 | 0 | 57 | 118.5964912280702 | 0 | 0
 |  | 2023-02-03 00:00:00 | 10 | 384.727 | 17 | 338.4494117647059 | 81 | 140.0001234567901 | 74 | 138.6817567567568
 |  | 2023-02-04 00:00:00 | 24 | 388.53375 | 31 | 390.0293548387097 | 116 | 159.7413793103448 | 100 | 159.1955
 |  | 2023-02-05 00:00:00 | 12 | 273.1058333333333 | 0 | 0 | 115 | 149.606347826087 | 89 | 136.9419101123595
 |  | 2023-02-06 00:00:00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  | 2023-02-07 00:00:00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  | 2023-02-08 00:00:00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  | 2023-02-09 00:00:00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  | 2023-02-10 00:00:00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  | 2023-02-11 00:00:00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  | 2023-02-12 00:00:00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  | 2023-02-13 00:00:00 | 21 | 0 | 0 | 0 | 114 | 0 | 0 | 0
 |  | 2023-02-14 00:00:00 | 0 | 0 | 0 | 0 | 124 | 0 | 0 | 0
 |  | 2023-02-15 00:00:00 | 0 | 0 | 0 | 0 | 125 | 0 | 0 | 0
 |  | 2023-02-16 00:00:00 | 0 | 0 | 0 | 0 | 127 | 0 | 0 | 0
 |  | 2023-02-17 00:00:00 | 11 | 0 | 19 | 0 | 121 | 0 | 98 | 0
 |  | 2023-02-18 00:00:00 | 14 | 0 | 16 | 0 | 135 | 0 | 101 | 0
 |  | 2023-02-19 00:00:00 | 20 | 0 | 0 | 0 | 121 | 0 | 105 | 0
 |  | 2023-02-20 00:00:00 | 9 | 0 | 0 | 0 | 147 | 0 | 0 | 0
 |  | 2023-02-21 00:00:00 | 0 | 0 | 0 | 0 | 128 | 0 | 0 | 0
 |  | 2023-02-22 00:00:00 | 0 | 0 | 0 | 0 | 122 | 0 | 0 | 0
 |  | 2023-02-23 00:00:00 | 0 | 0 | 0 | 0 | 130 | 0 | 0 | 0
 |  | 2023-02-24 00:00:00 | 18 | 0 | 10 | 0 | 108 | 0 | 80 | 0
 |  | 2023-02-25 00:00:00 | 22 | 0 | 19 | 0 | 131 | 0 | 110 | 0
 |  | 2023-02-26 00:00:00 | 23 | 0 | 0 | 0 | 135 | 0 | 84 | 0
 |  | 2023-02-27 00:00:00
 |  | 2023-02-28 00:00:00
 |  | 2023-03-01 00:00:00
 |  | 2023-03-02 00:00:00
 |  | 2023-03-03 00:00:00
 |  | 2023-03-04 00:00:00
 |  | 2023-03-05 00:00:00
 |  | 2023-03-06 00:00:00
 |  | 2023-03-07 00:00:00
 |  | 2023-03-08 00:00:00
 |  | 2023-03-09 00:00:00
 |  | 2023-03-10 00:00:00
 |  | 2023-03-11 00:00:00
 |  | 2023-03-12 00:00:00
 |  | 2023-03-13 00:00:00
 |  | 2023-03-14 00:00:00
 |  | 2023-03-15 00:00:00
 |  | 2023-03-16 00:00:00
 |  | 2023-03-17 00:00:00
 |  | 2023-03-18 00:00:00
 |  | 2023-03-19 00:00:00
 |  | 2023-03-20 00:00:00
 |  | 2023-03-21 00:00:00
 |  | 2023-03-22 00:00:00
 |  | 2023-03-23 00:00:00
 |  | 2023-03-24 00:00:00
 |  | 2023-03-25 00:00:00
 |  | 2023-03-26 00:00:00
 |  | 2023-03-27 00:00:00
 |  | 2023-03-28 00:00:00
 |  | 2023-03-29 00:00:00
 |  | 2023-03-30 00:00:00
 |  | 2023-03-31 00:00:00
 |  | 2023-04-01 00:00:00
 |  | 2023-04-02 00:00:00
 |  | 2023-04-03 00:00:00
 |  | 2023-04-04 00:00:00
 |  | 2023-04-05 00:00:00
 |  | 2023-04-06 00:00:00
 |  | 2023-04-07 00:00:00
 |  | 2023-04-08 00:00:00
 |  | 2023-04-09 00:00:00
 |  | 2023-04-10 00:00:00
 |  | 2023-04-11 00:00:00
 |  | 2023-04-12 00:00:00
 |  | 2023-04-13 00:00:00
 |  | 2023-04-14 00:00:00
 |  | 2023-04-15 00:00:00
 |  | 2023-04-16 00:00:00
 |  | 2023-04-17 00:00:00
 |  | 2023-04-18 00:00:00
 |  | 2023-04-19 00:00:00
 |  | 2023-04-20 00:00:00
 |  | 2023-04-21 00:00:00
 |  | 2023-04-22 00:00:00
 |  | 2023-04-23 00:00:00
 |  | 2023-04-24 00:00:00
 |  | 2023-04-25 00:00:00
 |  | 2023-04-26 00:00:00
 |  | 2023-04-27 00:00:00
 |  | 2023-04-28 00:00:00
 |  | 2023-04-29 00:00:00
 |  | 2023-04-30 00:00:00
 |  | 2023-05-01 00:00:00
 |  | 2023-05-02 00:00:00
 |  | 2023-05-03 00:00:00
 |  | 2023-05-04 00:00:00
 |  | 2023-05-05 00:00:00
 |  | 2023-05-06 00:00:00
 |  | 2023-05-07 00:00:00
 |  | 2023-05-08 00:00:00
 |  | 2023-05-09 00:00:00
 |  | 2023-05-10 00:00:00
 |  | 2023-05-11 00:00:00
 |  | 2023-05-12 00:00:00
 |  | 2023-05-13 00:00:00
 |  | 2023-05-14 00:00:00
 |  | 2023-05-15 00:00:00
 |  | 2023-05-16 00:00:00
 |  | 2023-05-17 00:00:00
 |  | 2023-05-18 00:00:00
 |  | 2023-05-19 00:00:00
 |  | 2023-05-20 00:00:00
 |  | 2023-05-21 00:00:00
 |  | 2023-05-22 00:00:00
 |  | 2023-05-23 00:00:00
 |  | 2023-05-24 00:00:00
 |  | 2023-05-25 00:00:00
 |  | 2023-05-26 00:00:00
 |  | 2023-05-27 00:00:00
 |  | 2023-05-28 00:00:00
 |  | 2023-05-29 00:00:00
 |  | 2023-05-30 00:00:00
 |  | 2023-05-31 00:00:00
 |  | 2023-06-01 00:00:00
 |  | 2023-06-02 00:00:00
 |  | 2023-06-03 00:00:00
 |  | 2023-06-04 00:00:00
 |  | 2023-06-05 00:00:00
 |  | 2023-06-06 00:00:00
 |  | 2023-06-07 00:00:00
 |  | 2023-06-08 00:00:00
 |  | 2023-06-09 00:00:00
 |  | 2023-06-10 00:00:00
 |  | 2023-06-11 00:00:00
 |  | 2023-06-12 00:00:00
 |  | 2023-06-13 00:00:00
 |  | 2023-06-14 00:00:00
 |  | 2023-06-15 00:00:00
 |  | 2023-06-16 00:00:00
 |  | 2023-06-17 00:00:00
 |  | 2023-06-18 00:00:00
 |  | 2023-06-19 00:00:00
 |  | 2023-06-20 00:00:00
 |  | 2023-06-21 00:00:00
 |  | 2023-06-22 00:00:00
 |  | 2023-06-23 00:00:00
 |  | 2023-06-24 00:00:00
 |  | 2023-06-25 00:00:00
 |  | 2023-06-26 00:00:00
 |  | 2023-06-27 00:00:00
 |  | 2023-06-28 00:00:00
 |  | 2023-06-29 00:00:00
 |  | 2023-06-30 00:00:00
 |  | 2023-07-01 00:00:00
 |  | 2023-07-02 00:00:00