Trying to come up with a way breakdown the total base to 50 positions, this is like a percentage thing but all done with a Formula.

[RudeBoy]

Okay Basically I have a Total Number in this case = 46212

I want to create a Formula that can change depending upon if my total number goes up or down.

each of the 50 places must be divisible evenly.

So to recap here is the total number and here is the 50 places that add up to this total number of 46212

1	1500
2	1400
3	1320
4	1284
5	1252
6	1120
7	1088
8	1064
9	1040
10	1016
11	1004
12	992
13	984
14	972
15	964
16	956
17	944
18	936
19	928
20	920
21	908
22	900
23	896
24	888
25	880
26	872
27	864
28	860
29	852
30	844
31	840
32	836
33	828
34	824
35	816
36	812
37	804
38	800
39	792
40	788
41	784
42	776
43	772
44	768
45	764
46	760
47	756
48	752
49	748
50	744
	46212

 

[Bosquedeguate]

What is the formula supposed to do? Where would you use it?
Using your example if 46212 is divided by 50 = 924 remainder of 12. So I could have 49 entries at 924, and 1 entry 936 (all values are even and add up to the total of 46212).
There is something that you have not provided in your problem description.
Please provide more details.

 

[RudeBoy]

What is the formula supposed to do? Where would you use it?
Using your example if 46212 is divided by 50 = 924 remainder of 12. So I could have 49 entries at 924, and 1 entry 936 (all values are even and add up to the total of 46212).
There is something that you have not provided in your problem description.
Please provide more details.

I need to break the 46212 down 50 times like this.
But if my TOTAL Changes 46212 then I want ever one of the 50 cells to change to match the percentage of the NEW total.
So that is where the challenge lies coming up with a percentage based on the total 46212 for each of the 50 cells

	1	1500	11	1004	21​	908	31	840	41	784
	2	1400	12	992	22	900​	32	836	42	776
	3	1320	13	984	23	896​	33	828	43	772
	4	1284	14	972	24	888​	34	824	44	768
	5	1252	15	964	25	880​	35	816	45	764
	6	1120	16	956	26	872​	36	812	46	760
	7	1088	17	944	27	864​	37	804	47	756
	8	1064	18	936	28	860​	38	800	48	752
	9	1040	19	928	29	852​	39	792	49	748
	10	1016	20	920	30	844​	40	788	50	744
									46212	Total

 

[Peter_SSs]

So, what about giving us an example where the 46212 has changed and explain how you got those results (or at a few of them) manually? XL2BB would be best.

Also, what exactly do you mean by this?

each of the 50 places must be divisible evenly.

 

[RudeBoy]

So, what about giving us an example where the 46212 has changed and explain how you got those results (or at a few of them) manually? XL2BB would be best.

Also, what exactly do you mean by this?

OK I tried to keep it simple but it seems I must go Deep.
I run a Bowling League these are Payouts for a 50 Team League. We sometimes have 50 Teams sometimes we have 32 Teams. Each Team comprises of 4 Players. So thus the Divisible by 4. I built this Breakdown years ago & do not want to keep figuring it out by hand as I already have a breakdown so I want to create % that can easily change if the total number of teams changes which in turn changes the total money amounts

Here is the pay scale in its TRUE for as I designed it Manually many many years ago

I was just trying to make a percentage formula instead of using my manual scale every time & simple deleting team number manually

 

[Bosquedeguate]

That information is extremely helpful.
Are your award amounts per player (or per team) fixed at the top levels (i.e. $1500/team or $375/player)? Is your starting point always $1500. Or is this a function of the award funds you have available to disperse?

I guess my question is how do you "manually" go about creating the award amounts?

 

[RudeBoy]

That information is extremely helpful.
Are your award amounts per player (or per team) fixed at the top levels (i.e. $1500/team or $375/player)? Is your starting point always $1500. Or is this a function of the award funds you have available to disperse?

I guess my question is how do you "manually" go about creating the award amounts?

I have been running my League since 2001 and have had different scales I have messed with over the years & this one is the best one that everyone loves & is very sustainable every year. 1st place TEAM get $1500 so $375 for each of the 4 Players & the scale goes down per place from there.

I am responsible for distributing all the funds me and my officers so we need to know what it is divisible by 4. Years ago we used to give change but that was not efficient so I always round up to eliminate the change & thus the scale I built years ago was developed.

The pandemic derailed my teams since then as I was at 40 solid now we are at 32 but growing as the pandemic is over so things will change in regards to numbers so I wanted some percentage formulas so I can easily run a trigger that can easily give me the number of teams & the payouts without having to do it manually each time deleting position etc.

I have some elaborate formula trigger if true that will give me the result I want or us leave a blank or - in its place like this

 

[Bosquedeguate]

This solution was actually on MrExcel a while back. It is a weighted distribution calculation. Here is what the numbers produced for your example of
50 teams and a Pot of 46212.

Book1
	A	B	C
1	Number of teams	50
2	Pot Total	46212
3	Wt Total	1275
4
5	Team Name	Team Award Weight	Award Amount
6	Team 1	50	1812
7	Team 2	49	1776
8	Team 3	48	1740
9	Team 4	47	1704
10	Team 5	46	1667
11	Team 6	45	1631
12	Team 7	44	1595
13	Team 8	43	1559
14	Team 9	42	1522
15	Team 10	41	1486
16	Team 11	40	1450
17	Team 12	39	1414
18	Team 13	38	1377
19	Team 14	37	1341
20	Team 15	36	1305
21	Team 16	35	1269
22	Team 17	34	1232
23	Team 18	33	1196
24	Team 19	32	1160
25	Team 20	31	1124
26	Team 21	30	1087
27	Team 22	29	1051
28	Team 23	28	1015
29	Team 24	27	979
30	Team 25	26	942
31	Team 26	25	906
32	Team 27	24	870
33	Team 28	23	834
34	Team 29	22	797
35	Team 30	21	761
36	Team 31	20	725
37	Team 32	19	689
38	Team 33	18	652
39	Team 34	17	616
40	Team 35	16	580
41	Team 36	15	544
42	Team 37	14	507
43	Team 38	13	471
44	Team 39	12	435
45	Team 40	11	399
46	Team 41	10	362
47	Team 42	9	326
48	Team 43	8	290
49	Team 44	7	254
50	Team 45	6	217
51	Team 46	5	181
52	Team 47	4	145
53	Team 48	3	109
54	Team 49	2	72
55	Team 50	1	36
56	Totals	1275	46212
Sheet1

Cell Formulas
Range	Formula
B1	B1	=50
B3	B3	=SUM(B6:B55)
C6:C55	C6	=ROUND((B6*$B$2)/$B$3,0)
B7:B55	B7	=B6-1
B56:C56	B56	=SUM(B6:B55)