Hi,
Is there a way to calculate a rate array from a total, if one has the counts? In the example below, I've used =SUMPRODUCT using rate 1 array and enrollment count array to get the monthly total. I'm wondering if there's any way to use a given monthly total and the count array to determine what rates were used under Rate 2. Any ideas?
Thank you!
Is there a way to calculate a rate array from a total, if one has the counts? In the example below, I've used =SUMPRODUCT using rate 1 array and enrollment count array to get the monthly total. I'm wondering if there's any way to use a given monthly total and the count array to determine what rates were used under Rate 2. Any ideas?
Thank you!
Jul-20 | Aug-20 | Sep-20 | Oct-20 | Nov-20 | Dec-20 | Jan-21 | Feb-21 | ||
---|---|---|---|---|---|---|---|---|---|
Rate 1 | | | | | | | | | |
Enrollment Tier 1 | 464.61 | 63 | 60 | 60 | 60 | 61 | 61 | 65 | 64 |
Enrollment Tier 2 | 1,005.96 | 44 | 42 | 43 | 42 | 43 | 40 | 40 | 40 |
Enrollment Tier 3 | 907.53 | 31 | 30 | 30 | 30 | 31 | 34 | 36 | 36 |
Enrollment Tier 4 | 1,448.89 | 40 | 43 | 42 | 42 | 41 | 43 | 43 | 43 |
Monthly Total | 159,621.70 | 159,655.09 | 159,212.16 | 158,206.20 | 159,135.41 | 161,737.90 | 165,411.40 | 164,946.79 | |
Rate 2 | |||||||||
Enrollment Tier 1 | ?? | 63 | 60 | 60 | 60 | 61 | 61 | 65 | 64 |
Enrollment Tier 2 | ?? | 44 | 42 | 43 | 42 | 43 | 40 | 40 | 40 |
Enrollment Tier 3 | ?? | 31 | 30 | 30 | 30 | 31 | 34 | 36 | 36 |
Enrollment Tier 4 | ?? | 40 | 43 | 42 | 42 | 41 | 43 | 43 | 43 |
Monthly Total | 309,377.24 | 310,705.39 | 295,170.12 | 280,411.61 | 266,391.03 | 253,071.48 | 240,417.91 | 228,397.01 |