Update Equally Distributed Formula

P

Pestomania

Active Member
Joined
May 30, 2018
Messages
332
Office Version
  1. 365
Platform
  1. Windows
Cell V2 has a formula that does an equal distribution but I would like to make it to where I do not have to add it to every January month field.

As you can see in V3, AH3, and so on, I have to paste the distribution formula into the field. I'd like the formula to auto-fill the table based on the value found in the A1:G26 table.


Book4
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARAS
1Program'23'24'25'26'27'28Batch SizeJan '23Feb '23Mar '23Apr '23May '23Jun '23Jul '23Aug '23Sep '23Oct '23Nov '23Dec '23Jan '24Feb '24Mar '24Apr '24May '24Jun '24Jul '24Aug '24Sep '24Oct '24Nov '24Dec '24
2Program 1805252608832Program 14Program 1488488488488488488488488
3Program 21620201325Program 21Program 2112112112112122122122122
4Program 3289238374391204272Program 317Program 3171734173417173417341734171717171734171717171734
5Program 4162218144244Program 42Program 4022022022022022222222222
6Program 512550109012515Program 55Program 5101010101010101010101015055555055555
7Program 610510515165360240Program 615Program 601501501515015015150150150151501501515
8Program 72504060160120180Program 710Program 72020202020202020202020300010001000100010
Sheet1
Cell Formulas
RangeFormula
U2:U26U2=UNIQUE(A2:A26)
V1:CO1V1=TEXT(DATE("20"&RIGHT(B1,2),SEQUENCE(,12*COUNTIF(A1:G1,"<>'")-12),1),"mmm 'yy")
V2:CO2V2=INDEX(LET(z,SUMPRODUCT(($A$2:$A$26=$U2)*($B$1:$G$1=RIGHT(V$1,3))*$B$2:$G$26)/VLOOKUP($U2,$N$2:$O$26,2,FALSE),b,SEQUENCE(1,12*COUNTIF($A$1:$G$1,"<>'")-12),c,z/12*b,d,INT(z/12)*SEQUENCE(1,12*COUNTIF($A$1:$G$1,"<>'")-12,1,0),e,MOD(c,1),f,IF(INDEX(e,b)<INDEX(e,b-1),d+1,d),f)*VLOOKUP($U2,$N$2:$O$26,2,FALSE),MOD(SEQUENCE(1,12*COUNTIF($A$1:$G$1,"<>'")-12,0),12)+1)
V3:AS8V3=INDEX(LET(z,SUMPRODUCT(($A$2:$A$26=$U3)*($B$1:$G$1=RIGHT(V$1,3))*$B$2:$G$26)/VLOOKUP($U3,$N$2:$O$26,2,FALSE),b,SEQUENCE(1,12),c,z/12*b,d,INT(z/12)*SEQUENCE(1,12,1,0),e,MOD(c,1),f,IF(INDEX(e,b)<INDEX(e,b-1),d+1,d),f)*VLOOKUP($U3,$N$2:$O$26,2,FALSE),MOD(SEQUENCE(1,12,0),12)+1)
Dynamic array formulas.
 
Sort by date Sort by votes

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
So if you have these two table:

"Dashboard"
Prestons Playground for Modeling.xlsx
BCDEFGH
2Program'23'24'25'26'27'28
3Program 1805252608832
4Program 21620201325
5Program 3289238374391204272
6Program 4162218144244
7Program 512550109012515
8Program 610510515165360240
9Program 72504060160120180
Dashboard


"BatchSize"
Prestons Playground for Modeling.xlsx
AB
1ProgramBatch Size
2Program 14
3Program 21
4Program 317
5Program 42
6Program 55
7Program 615
8Program 710
Data Tables
Cell Formulas
RangeFormula
A2:A8A2=CHOOSEROWS(UNIQUE(Dashboard[Program]),ROWS($N$2:$N2))


Then these formulas should give you the result you want:

Prestons Playground for Modeling.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBU
1Jan '23Feb '23Mar '23Apr '23May '23Jun '23Jul '23Aug '23Sep '23Oct '23Nov '23Dec '23Jan '24Feb '24Mar '24Apr '24May '24Jun '24Jul '24Aug '24Sep '24Oct '24Nov '24Dec '24Jan '25Feb '25Mar '25Apr '25May '25Jun '25Jul '25Aug '25Sep '25Oct '25Nov '25Dec '25Jan '26Feb '26Mar '26Apr '26May '26Jun '26Jul '26Aug '26Sep '26Oct '26Nov '26Dec '26Jan '27Feb '27Mar '27Apr '27May '27Jun '27Jul '27Aug '27Sep '27Oct '27Nov '27Dec '27Jan '28Feb '28Mar '28Apr '28May '28Jun '28Jul '28Aug '28Sep '28Oct '28Nov '28Dec '28
2Program 1488488488488444444444448444444444448444844484448488888488888044044044044
3Program 2112112112112122122122122122122122122000000000001000100010001222222222223
4Program 3171734173417173417341734171717171734171717171734173434343434173434343434173434343434343434343434171717171717171717171717171734171734171734171734
5Program 4022022022022022222222222022202220222020202202022244424442444244444244444
6Program 5101010101010101010101015055555055555000005000005510510510510510510101010101010101010101015000500050005
7Program 601501501515015015150150150151501501515000000000001501515151515151515151515303030303030303030303030151530151530151530151530
8Program 72020202020202020202020300010001000100010010010010010010010101020101020101020101020101010101010101010101010102010201020102010201020
Deliverables Table
Cell Formulas
RangeFormula
A2:A8A2=UNIQUE(Dashboard[Program])
B1:BU1B1=LET(cd,EDATE(DATE(SUBSTITUTE(Dashboard[[#Headers],[''23]],"'","")+2000,1,1),SEQUENCE(,COUNTA(Dashboard[[#Headers],[''23]:[''28]])*12,0)), cd )
B2:BU8B2=LET( header, DROP(Dashboard[#Headers],,1), program, $A2, y, DATE(SUBSTITUTE(header,"'","")+2000,1,1), cd, EDATE(DATE(SUBSTITUTE(CHOOSECOLS(header,1),"'","")+2000,1,1),SEQUENCE(,COUNTA(header)*12,0)), m, MONTH(cd), s, BYCOL(cd,LAMBDA(x,FILTER(FILTER(DROP(Dashboard,,1),Dashboard[Program]=program),YEAR(y)=YEAR(x)))), b, XLOOKUP($A2,BatchSize2[Program],BatchSize2[Batch Size],,0), v, (INT((s/12*m)/b)-INT((s/12*(m-1))/b))*b, v )
Dynamic array formulas.


Here is the working book:
Prestons Playground for Modeling Felix.zip
 
Upvote 1
Solution
That works amazing!!

Only question:

Excel Formula: 
=CHOOSEROWS(UNIQUE(Dashboard[Program]),ROWS($N$2:$N2))

What is the Rows($N$2:$N2)? Those cells are empty from what I can tell.
 
Upvote 0
The formula
Excel Formula: 
=ROWS($N$2:$N2)
gives you a sequence of numbers starting from 1 and incrementing as you drag down:

Prestons Playground for Modeling Felix.xlsx
HI
211
322
433
544
655
766
877
Data Tables
Cell Formulas
RangeFormula
H2:H8H2=ROWS($H$2:$H2)
I2:I8I2=ROWS($N$2:$N2)


Since Dynamic formulas that return more than one array item are incompatible with Tables we use this to filter the result from the UNIQUE function

You could use any column, it doesn't matter.
 
Upvote 0
felixstraube said:
The formula
Excel Formula: 
=ROWS($N$2:$N2)
gives you a sequence of numbers starting from 1 and incrementing as you drag down:

Prestons Playground for Modeling Felix.xlsx
HI
211
322
433
544
655
766
877
Data Tables
Cell Formulas
RangeFormula
H2:H8H2=ROWS($H$2:$H2)
I2:I8I2=ROWS($N$2:$N2)


Since Dynamic formulas that return more than one array item are incompatible with Tables we use this to filter the result from the UNIQUE function
Click to expand...
Oh okay, cool! Thank you for the explanation. What happens if something is in N2 all of a sudden? I am planning to add numerous more data tables on this tab. Is that going to mess anything up? I tried testing a few different types of data, but wanted to check with the experts.
 
Upvote 0
Nothing will happen, but to avoid reference problems (if you delete exactly that column) you could change that to the same column as Progam in you BatchSize table like this:

Prestons Playground for Modeling Felix.xlsx
AB
1ProgramBatch Size
2Program 14
3Program 21
4Program 317
5Program 42
6Program 55
7Program 615
8Program 710
Data Tables
Cell Formulas
RangeFormula
A2:A8A2=CHOOSEROWS(UNIQUE(Dashboard[Program]),ROWS($A$2:$A2))
 
Upvote 0
felixstraube said:
Nothing will happen, but to avoid reference problems (if you delete exactly that column) you could change that to the same column as Progam in you BatchSize table like this:

Prestons Playground for Modeling Felix.xlsx
AB
1ProgramBatch Size
2Program 14
3Program 21
4Program 317
5Program 42
6Program 55
7Program 615
8Program 710
Data Tables
Cell Formulas
RangeFormula
A2:A8A2=CHOOSEROWS(UNIQUE(Dashboard[Program]),ROWS($A$2:$A2))
Click to expand...
Oh awesome! So it doesn't so much care what is there, just that it has a row to reference!

Thank you very much and sorry for all of the confusion. This has been a massive project and I am not even 5% done but I think a lot of these formulas can help me piece together the rest of the excel workbook. In total, this workbook has over 200,000 calculations. I am trying to make it easier to manage.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Compact the formula instead of repeating
Replies
8
Views
152
Stephen_IV
S
P
  • Question Question
Creating Sub Totals for a Sort, Unique, Filter, Choose Cols dynamically
Replies
6
Views
350
ExcelToDAX
E
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
319
etaf
etaf
R
  • Solved
How to independently sort multiple columns of this SEQUENCE output without effecting the positions of the columns and of the blanks?
2
Replies
15
Views
580
Rnkhch
R
L
  • Question Question
Help with formula to keep leading zero in cell - cant format as date
Replies
4
Views
72
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top