Way to create rate array from total and counts (opposite of sumproduct)?

[PRoozi]

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!

		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​

 

[steve the fish]

No there isnt. There would be infinite possible solutions.

 

[jasonb75]

You certainly wouldn't be able to do it with simple formulas.

In theory it should be possible, in practice it would be unrealistic. You would need to test every Tier with every amount from 0.01 to the monthly total in penny increments, and that is assuming that the rates are always rounded to the nearest penny, if they are calculated in a way that allows fractions of a penny then you have no hope.

An observant person could possibly work it out (with accurate totals). I had a quick look at your rate 2 table, but the figures do not add up properly.
Tier 4 calculates at a unit rate of 30,293.78 and Tier 2 calculates at a unit rate of 14,758.51, both of which are impossible to match to the counts and totals in your sheet.

I didn't even look at the other 2 tiers.

 

[PRoozi]

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!

		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​

Please disregard the totals under rate 2, they were subject to a 95% rule from prior month. If you want the actual data to check:

		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
		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
		153,994.43​	173,138.09​	172,701.09​	171,631.71​	172,933.03​	175,160.50​	178,573.79​	179,068.30​

Thanks!

 

[PRoozi]

You certainly wouldn't be able to do it with simple formulas.

In theory it should be possible, in practice it would be unrealistic. You would need to test every Tier with every amount from 0.01 to the monthly total in penny increments, and that is assuming that the rates are always rounded to the nearest penny, if they are calculated in a way that allows fractions of a penny then you have no hope.

An observant person could possibly work it out (with accurate totals). I had a quick look at your rate 2 table, but the figures do not add up properly.
Tier 4 calculates at a unit rate of 30,293.78 and Tier 2 calculates at a unit rate of 14,758.51, both of which are impossible to match to the counts and totals in your sheet.

I didn't even look at the other 2 tiers.

Thank you!

 

[steve the fish]

Ah ok i hadnt looked long enough. Well if they arent independant events then its possible however the last two columns mean Tier 1 is negative. Is that possible?

 

[jasonb75]

Even with the new data, it still doesn't add up (and that's with Tier 1 negative as @steve the fish has observed).

Steve, would you agree with this? Tier 3 not adding up. Yellow cells show the pairs that I've used to identify the rates for those tiers. Bottom row values should all be the same if the figures are correct.

Book1
	A	B	C	D	E	F	G	H	I	J
16		Rate 2
17	Enrollment Tier 1	-494.50	63	60	60	60	61	61	65	64
18	Enrollment Tier 2	1,069.38	44	42	43	42	43	40	40	40
19	Enrollment Tier 3		31	30	30	30	31	34	36	36
20	Enrollment Tier 4	1,506.38	40	43	42	42	41	43	43	43
21	Total		153,994.43	173,138.09	172,701.09	171,631.71	172,933.03	175,160.50	178,573.79	179,068.30
22	Comparative		76,154.42	80,018.30	79,581.30	78,511.92	77,580.42	77,385.04	75,407.04	75,901.54
23	Difference		77,840.01	93,119.79	93,119.79	93,119.79	95,352.61	97,775.46	103,166.75	103,166.76
24	Tier 3 (theoretical)		2,510.97	3,103.99	3,103.99	3,103.99	3,075.89	2,875.75	2,865.74	2,865.74
Sheet2

Cell Formulas
Range	Formula
C22:J22	C22	=SUMPRODUCT($B$17:$B$20,C17:C20)
C23:J23	C23	=C21-C22
C24:J24	C24	=C23/C19

 

[steve the fish]

Yes exactly what i got. If the rates cannot change over time its an impossible calc. You can deduce Tier 1 from Jan and Feb, Tier 2 from Sept and Oct, and Tier 4 from Aug and Oct. Tier 3 then is impossible to deduce as it doesnt fit.

 

[jasonb75]

If the rates cannot change over time its an impossible calc.

If the rates can change over time then it would be equally impossible as there would be no definite factors to start working from in the information that we have available.

 

[steve the fish]

If the rates can change over time then it would be equally impossible as there would be no definite factors to start working from in the information that we have available.

Indeed. Either way its not possible from whats been given.