Spreadsheet: 752442d1634755021-conversion-of-formula-to-a-simpler-one-jaytee-sumproduct2
Sheet: Step2

Step 2: Volume indices from previous year=100 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | new | new | new | new | new | new | new | new
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Sales |  |  |  | Weights
 |  | Variable | Measure | Price Index | Q1 2017 | Q2 2017 | Q3 2017 | Q4 2017 | 2017 | Q1 2018 | Q2 2018 | Q3 2018 | Q4 2018 | 2018 | Q1 2019 | Q2 2019 | Q3 2019 | Q4 2019 | 2019 |  | 2018 | 2019 | 2020
41 | 4100 |  |  |  |  |  |  |  |  | 103.9117981845692 | 105.3290397366334 | 103.3304015733651 | 110.764688095766 | 105.8339818975834 | 91.74708486988287 | 99.5820217076876 | 113.8495304128018 | 114.4502583349249 | 104.9072238313243
42 | 4210 |  |  |  |  |  |  |  |  | 107.4937593594294 | 96.24211931895856 | 150.6334238439909 | 147.68102071915 | 125.5125808103822 | 110.6730602008997 | 69.35369538379548 | 73.5500075555305 | 52.77059670676201 | 76.5868399617469
42 | 4220 |  |  |  |  |  |  |  |  | 90.50951497053478 | 118.6500086305888 | 97.12494523463965 | 90.87637147425076 | 99.29021007750349 | 158.0539807950555 | 148.0719686472046 | 153.3117166557393 | 149.3404861826339 | 152.1945380701584
42 | 4290 |  |  |  |  |  |  |  |  | 89.97023667021028 | 117.1701901203173 | 91.8402757987154 | 132.7328871603236 | 107.9283974373916 | 81.78946010547253 | 70.35385178802672 | 73.05739115883064 | 93.8250195016332 | 79.75643063849077
47 | 4772 |  |  |  |  |  |  |  |  | 98.3988608356334 | 93.99335932377443 | 89.92651615729993 | 130.8828111815123 | 103.300386874555 | 80.63644788232257 | 93.12093970963544 | 98.30124070262883 | 133.8588251985571 | 101.479363373286
49 | 4922 |  |  |  |  |  |  |  |  | 93.17835835569879 | 92.14856740941678 | 106.7737583254254 | 111.2927149900054 | 100.8483497701366 | 96.27116208246669 | 88.96951945182775 | 89.93124246780579 | 96.38397013334749 | 92.88897353386194
49 | 4923 |  |  |  |  |  |  |  |  | 93.74703308858636 | 92.41879222638082 | 79.46226750163397 | 83.62922763785902 | 87.31433011361506 | 94.92848708490106 | 89.0045953198142 | 86.8698248889485 | 95.43834243111314 | 91.56031243119422
49 | 4924 |  |  |  |  |  |  |  |  | 122.7393078451228 | 107.027010439584 | 72.20794308765488 | 120.0222392780291 | 105.4991251625977 | 123.600655234983 | 97.09962240132715 | 70.12168146176984 | 113.2145370160207 | 101.0091240285252
50 | 5010 |  |  |  |  |  |  |  |  | 95.49026595937222 | 95.13954680337181 | 93.44999096983236 | 100.7842215496389 | 96.21600632055382 | 96.48195248685549 | 94.6699573386023 | 98.09207500148452 | 108.3710160158088 | 99.40375021068778
51 | 5110 |  |  |  |  |  |  |  |  | #[NAME]! | #[NAME]! | #[NAME]! | #[NAME]! | #[NAME]! | #[NAME]! | #[NAME]! | #[NAME]! | #[NAME]! | #[NAME]!
52 | 5210 |  |  |  |  |  |  |  |  | 129.1347856825677 | 132.1087251421816 | 122.6267412015551 | 129.9047584442448 | 128.4437526176373 | 109.0273176725359 | 110.2886379343883 | 113.7140632790157 | 109.8508204127023 | 110.7202098246606
52 | 5224 |  |  |  |  |  |  |  |  | 108.3319439117965 | 100.045804712453 | 102.5544232395279 | 109.6476955703945 | 105.144966858543 | 91.932269064755 | 125.734437942253 | 85.19639364738511 | 112.4435682409318 | 103.8266672238312
52 | 5229 |  |  |  |  |  |  |  |  | 101.3861408641178 | 105.0638915397906 | 154.3389864119223 | 164.9756787563599 | 131.4411743930476 | 95.52342914206803 | 117.9528519381024 | 158.6801737993889 | 161.464357924583 | 133.4052032010356
56 | 5611 |  |  |  |  |  |  |  |  | 96.68636001824483 | 103.7827496619818 | 90.24312653414482 | 100.227427176059 | 97.7349158476076 | 103.7399693005557 | 95.08999194887218 | 85.45751716060384 | 100.2081405751778 | 96.12390474630237
56 | 5612 |  |  |  |  |  |  |  |  | 94.17234365396375 | 91.34632435017333 | 84.5130366782422 | 93.56194715069624 | 90.89841295826888 | 102.6423627852928 | 91.78121537035285 | 86.76413676702064 | 100.2956788418878 | 95.37084844113852
56 | 5613 |  |  |  |  |  |  |  |  | 101.5385410941817 | 98.56305157079747 | 100.8670339023755 | 106.5141490142478 | 101.8706938954006 | 104.2105504610647 | 100.8138508019135 | 102.7264483298566 | 109.8814166695879 | 104.4080665656057
79 | 7911 |  |  |  |  |  |  |  |  | 79.0212837777764 | 108.4259535823 | 86.10248511356228 | 89.95664048796527 | 90.87659074040099 | 86.97340908798556 | 146.7960259796404 | 70.43154675659828 | 74.15438030975521 | 94.58884053349486
79 | 7912 |  |  |  |  |  |  |  |  | 181.6957599731133 | 102.7734460384979 | 50.18742067810427 | 159.5404951952979 | 123.5492804712534 | 219.7161365118487 | 112.7343948154427 | 55.40672460188669 | 175.33859501488 | 140.7989627360145
80 | 8010 |  |  |  |  |  |  |  |  | #[NAME]! | #[NAME]! | #[NAME]! | #[NAME]! | #[NAME]! | #[NAME]! | #[NAME]! | #[NAME]! | #[NAME]! | #[NAME]!
81 | 8100 |  |  |  |  |  |  |  |  | #[NAME]! | #[NAME]! | #[NAME]! | #[NAME]! | #[NAME]! | #[NAME]! | #[NAME]! | #[NAME]! | #[NAME]! | #[NAME]!
85 | 8514 |  |  |  |  |  |  |  |  | 99.9567608937656 | 96.08603945661208 | 87.69606893881858 | 94.22307844263925 | 94.49048693295887 | 99.91439181262733 | 94.00609066932057 | 92.27533494582734 | 96.85642356249731 | 95.76306024756815
85 | 8530 |  |  |  |  |  |  |  |  | 127.7791468705725 | 80.28301322839353 | 115.1110774700229 | 70.0827854367145 | 98.31400575142587 | 134.7760155159743 | 74.87277222572567 | 127.3850137184731 | 81.7341247490895 | 104.6919815523156
85 | 8544 |  |  |  |  |  |  |  |  | 100.3936861371161 | 91.3632081892267 | 89.21498953895075 | 88.79789586392184 | 92.44244493230383 | 92.19988038767939 | 80.77509343831932 | 85.58049702851666 | 92.68121619719273 | 87.80917176292702
85 | 8548 |  |  |  |  |  |  |  |  | 128.1169511931263 | 84.94470810327509 | 87.40168145546926 | 103.1073889501692 | 100.89268242551 | 95.62587003335472 | 73.04881504653031 | 72.81074524492263 | 86.72346890148607 | 82.05222480657342
86 | 8610 |  |  |  |  |  |  |  |  | 105.3559741551492 | 97.14444722007165 | 89.76523230203021 | 101.8750805298997 | 98.53518355178768 | 104.8093940087807 | 97.02361846033084 | 89.26482607992196 | 109.5674667155668 | 100.1663263161501
86 | 8621 |  |  |  |  |  |  |  |  | 103.485904182816 | 85.89172252746894 | 84.07604302124447 | 93.27622103664119 | 91.68247269204264 | 96.07017788955491 | 100.4714920491395 | 90.71499677039877 | 98.70071302365697 | 96.48934493318754
86 | 8622 |  |  |  |  |  |  |  |  | 79.4330500845747 | 99.90869783259852 | 80.02609085541795 | 86.7811585212005 | 86.53724932344792 | 102.7157985401171 | 104.0478311555387 | 97.82335832674801 | 105.6105782443808 | 102.5493915666961
86 | 8623 |  |  |  |  |  |  |  |  | 104.9342366305025 | 109.6854137169057 | 94.34916585340844 | 113.7450855192332 | 105.6784754300124 | 106.2353329249853 | 110.0063348098122 | 94.27497669459439 | 105.5844699647868 | 104.0252785985446
86 | 8690 |  |  |  |  |  |  |  |  | 86.90669027119726 | 81.05291874357088 | 84.21556913366639 | 75.20400672914039 | 81.84479621939374 | 86.09075931466174 | 80.44550380103787 | 96.76101179475341 | 90.70973290161632 | 88.50175195301735
91 | 9103 |  |  |  |  |  |  |  |  | 149.183172048484 | 95.76019245879004 | 112.5079940128585 | 217.0792766857649 | 143.6326588014743 | 190.5790038540437 | 168.065729696087 | 85.89969336256816 | 143.1001678921801 | 146.9111487012197
93 | 9312 |  |  |  |  |  |  |  |  | 88.34067098416497 | 83.80465732305838 | 85.94519210276913 | 99.56217781089568 | 89.41317455522204 | 105.4647832447999 | 92.2827619078628 | 122.6729606663643 | 130.33710380021 | 112.6894024048093
93 | 9321 |  |  |  |  |  |  |  |  | 82.6203816550938 | 87.90082579625278 | 116.6313555602285 | 70.50668642742373 | 89.41481235974969 | 81.54502857722562 | 98.88548346653955 | 98.18097651237207 | 80.44151614984281 | 89.763251176495
93 | 9329 |  |  |  |  |  |  |  |  | 107.2742015419358 | 114.4377820439508 | 104.0073615753178 | 111.6572253397441 | 109.3441426252371 | 103.0749105181039 | 101.2716385232893 | 91.09375308432902 | 109.1806663559523 | 101.1552421204186
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Deltas / Reconciliation:
aggregated by the groups |  |  |  |  |  |  |  |  |  | original formula |  |  |  |  |  |  |  |  |  |  | changed formula
41
42
43
44
45
46
47
49
50
51
52
56
64
68
69
70
71
73
77
78
79
80
81
85
86
91
93
95
96