Update Equally Distributed Formula

P

Pestomania

Active Member
Joined
May 30, 2018
Messages
292
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.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
How about this?:
One little change i made. The row B1:G1 are actual dates the first of january of each year, formatted to "yyyy".

BatchDistrubutedEqually.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCACBCCCDCECFCGCHCICJCKCLCMCNCO
1Program202320242025202620272028Batch SizeJan-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 1805252608832Program 14Program 1488488488488444444444448444444444448444844484448488888488888044044044044
3Program 21620201325Program 21Program 2112112112112122122122122122122122122000000000001000100010001222222222223
4Program 3289238374391204272Program 317Program 3171734173417173417341734171717171734171717171734173434343434173434343434173434343434343434343434171717171717171717171717171734171734171734171734
5Program 4162218144244Program 42Program 4022022022022022222222222022202220222020202202022244424442444244444244444
6Program 512550109012515Program 55Program 5101010101010101010101015055555055555000005000005510510510510510510101010101010101010101015000500050005
7Program 610510515165360240Program 615Program 601501501515015015150150150151501501515000000000001501515151515151515151515303030303030303030303030151530151530151530151530
8Program 72504060160120180Program 710Program 72020202020202020202020300010001000100010010010010010010010101020101020101020101020101010101010101010101010102010201020102010201020
Sheet2
Cell Formulas
RangeFormula
U2:U8U2=$A$2:$A$8
V1:CO1V1=LET(cd,EDATE($B$1,SEQUENCE(,COUNT(B1:G1)*12,0)), cd)
V2:CO8V2=LET( cd,EDATE($B$1,SEQUENCE(,COUNT($B$1:$G$1)*12,0)), m,MONTH(cd), s, BYCOL(cd,LAMBDA(x,FILTER($B2:$G2,YEAR($B$1:$G$1)=YEAR(x)))), b, $O2, v,(INT((s/12*m)/b)-INT((s/12*(m-1))/b))*b, v )
Dynamic array formulas.
 
Upvote 0
felixstraube said:
How about this?:
One little change i made. The row B1:G1 are actual dates the first of january of each year, formatted to "yyyy".

BatchDistrubutedEqually.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCACBCCCDCECFCGCHCICJCKCLCMCNCO
1Program202320242025202620272028Batch SizeJan-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 1805252608832Program 14Program 1488488488488444444444448444444444448444844484448488888488888044044044044
3Program 21620201325Program 21Program 2112112112112122122122122122122122122000000000001000100010001222222222223
4Program 3289238374391204272Program 317Program 3171734173417173417341734171717171734171717171734173434343434173434343434173434343434343434343434171717171717171717171717171734171734171734171734
5Program 4162218144244Program 42Program 4022022022022022222222222022202220222020202202022244424442444244444244444
6Program 512550109012515Program 55Program 5101010101010101010101015055555055555000005000005510510510510510510101010101010101010101015000500050005
7Program 610510515165360240Program 615Program 601501501515015015150150150151501501515000000000001501515151515151515151515303030303030303030303030151530151530151530151530
8Program 72504060160120180Program 710Program 72020202020202020202020300010001000100010010010010010010010101020101020101020101020101010101010101010101010102010201020102010201020
Sheet2
Cell Formulas
RangeFormula
U2:U8U2=$A$2:$A$8
V1:CO1V1=LET(cd,EDATE($B$1,SEQUENCE(,COUNT(B1:G1)*12,0)), cd)
V2:CO8V2=LET( cd,EDATE($B$1,SEQUENCE(,COUNT($B$1:$G$1)*12,0)), m,MONTH(cd), s, BYCOL(cd,LAMBDA(x,FILTER($B2:$G2,YEAR($B$1:$G$1)=YEAR(x)))), b, $O2, v,(INT((s/12*m)/b)-INT((s/12*(m-1))/b))*b, v )
Dynamic array formulas.
Click to expand...
Is there any way to use similar to what I have available? I unfortunately cannot change the date like fields due to ownership over the formatting not being mine.

Here is the "actual" formula that I am trying to coordinate the changes from the above tables (which is an example) to the actual details. I have to maintain some of the automation that certain lookup fields allow me to maintain.

This is hard to explain since I cannot upload the actual file.

Excel Formula: 
=INDEX(LET(z,SUMPRODUCT((Dashboard[Program]='Deliverables Table'!$A2)*(Dashboard[[#Headers],[''23]:[''43]]=RIGHT('Deliverables Table'!B$1,3))*Dashboard[[''23]:[''43]])/VLOOKUP($A2,'Data Tables'!$A$2:$B40,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($A2,'Data Tables'!$A$2:$B40,2,FALSE),MOD(SEQUENCE(1,12,0),12)+1)
 
Upvote 0
How about this?:

BatchDistrubutedEqually.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCACBCCCDCECFCGCHCICJCKCLCMCNCO
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-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 1805252608832Program 14Program 1488488488488444444444448444444444448444844484448488888488888044044044044
3Program 21620201325Program 21Program 2112112112112122122122122122122122122000000000001000100010001222222222223
4Program 3289238374391204272Program 317Program 3171734173417173417341734171717171734171717171734173434343434173434343434173434343434343434343434171717171717171717171717171734171734171734171734
5Program 4162218144244Program 42Program 4022022022022022222222222022202220222020202202022244424442444244444244444
6Program 512550109012515Program 55Program 5101010101010101010101015055555055555000005000005510510510510510510101010101010101010101015000500050005
7Program 610510515165360240Program 615Program 601501501515015015150150150151501501515000000000001501515151515151515151515303030303030303030303030151530151530151530151530
8Program 72504060160120180Program 710Program 72020202020202020202020300010001000100010010010010010010010101020101020101020101020101010101010101010101010102010201020102010201020
Sheet2
Cell Formulas
RangeFormula
U2:U8U2=$A$2:$A$8
V1:CO1V1=LET(cd,EDATE(DATE(SUBSTITUTE($B$1,"'","")+2000,1,1),SEQUENCE(,COUNTA($B$1:$G$1)*12,0)), cd)
V2:CO8V2=LET( y, DATE(SUBSTITUTE($B$1:$G$1,"'","")+2000,1,1), cd,EDATE(DATE(SUBSTITUTE($B$1,"'","")+2000,1,1),SEQUENCE(,COUNTA($B$1:$G$1)*12,0)), m,MONTH(cd), s, BYCOL(cd,LAMBDA(x,FILTER($B2:$G2,YEAR(y)=YEAR(x)))), b, $O2, v,(INT((s/12*m)/b)-INT((s/12*(m-1))/b))*b, v )
Dynamic array formulas.
 
Upvote 0
This works great!

A few things I am still trying to act right:

This $B$1:$G$1 area is a "named" field which is called Dashboard[[Headers]] in the actual file. But the actual table has these headers:
Program'23'24'25'26'27'28

Is there a way to make $B$1:$G$1 be a callout to the headers but disregard program? I am trying to make this as "automated" as possible so when I add columns to the table, the system keeps going wihtout interaction.

The other things I am struggling with:

$B2:$G2 is actually Dashboard[#All] or Dashboard[[''23]:[''43]] (this still limits it to 2043, so if I add 2044, nothing happens) on the real table, but when I do that, it errors out.

$O2 is now the vlookup you see below.

Excel Formula: 
 =LET(y, DATE(SUBSTITUTE(Dashboard[[#Headers],[''23]:[''43]],"'","")+2000,1,1),cd,EDATE(DATE(SUBSTITUTE(Dashboard[[#Headers],[''23]],"'","")+2000,1,1),SEQUENCE(,COUNTA(Dashboard[[#Headers],[''23]:[''43]])*12,0)),m,MONTH(cd),s, BYCOL(cd,LAMBDA(x,FILTER(Dashboard!$C$3:$W$3,YEAR(y)=YEAR(x)))),b, VLOOKUP($A2,'Data Tables'!A:B,2,FALSE),v,(INT((s/12*m)/b)-INT((s/12*(m-1))/b))*b,v)
 
Upvote 0
Maybe you can share the actual file (upload it to a cloud storage) and I'll have a look.
 
Upvote 0
felixstraube said:
Maybe you can share the actual file (upload it to a cloud storage) and I'll have a look.
Click to expand...
Hi, unfortunately I cannot because the file has proprietary information. I will try and figure out what is going on later today and see if I can finagle an edit.

Thank you so much for your help!
 
Upvote 0
Ok if your table is named Dashboard (A1:G8) then how about this?:
The only thing that is not pulled from that table is the batch size from column O. Is that part of that table as well?

BatchDistrubutedEqually.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCACBCCCDCECFCGCHCICJCKCLCMCNCO
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-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 1805252608832Program 14Program 1488488488488444444444448444444444448444844484448488888488888044044044044
3Program 21620201325Program 21Program 2112112112112122122122122122122122122000000000001000100010001222222222223
4Program 3289238374391204272Program 317Program 3171734173417173417341734171717171734171717171734173434343434173434343434173434343434343434343434171717171717171717171717171734171734171734171734
5Program 4162218144244Program 42Program 4022022022022022222222222022202220222020202202022244424442444244444244444
6Program 512550109012515Program 55Program 5101010101010101010101015055555055555000005000005510510510510510510101010101010101010101015000500050005
7Program 610510515165360240Program 615Program 601501501515015015150150150151501501515000000000001501515151515151515151515303030303030303030303030151530151530151530151530
8Program 72504060160120180Program 710Program 72020202020202020202020300010001000100010010010010010010010101020101020101020101020101010101010101010101010102010201020102010201020
Sheet2 (2)
Cell Formulas
RangeFormula
U2:U8U2=Dashboard[Program]
V1:CO1V1=LET(cd,EDATE(DATE(SUBSTITUTE(Dashboard[[#Headers],[''23]],"'","")+2000,1,1),SEQUENCE(,COUNTA(Dashboard[[#Headers],[''23]:[''28]])*12,0)), cd )
V2:CO8V2=LET( y, DATE(SUBSTITUTE(Dashboard[[#Headers],[''23]:[''28]],"'","")+2000,1,1), cd,EDATE(DATE(SUBSTITUTE(Dashboard[[#Headers],[''23]],"'","")+2000,1,1),SEQUENCE(,COUNTA(Dashboard[[#Headers],[''23]:[''28]])*12,0)), m,MONTH(cd), s, BYCOL(cd,LAMBDA(x,FILTER(Dashboard[@[''23]:[''28]],YEAR(y)=YEAR(x)))), b, $O2, v,(INT((s/12*m)/b)-INT((s/12*(m-1))/b))*b, v )
Dynamic array formulas.
 
Upvote 1
felixstraube said:
Ok if your table is named Dashboard (A1:G8) then how about this?:
The only thing that is not pulled from that table is the batch size from column O. Is that part of that table as well?

BatchDistrubutedEqually.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCACBCCCDCECFCGCHCICJCKCLCMCNCO
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-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 1805252608832Program 14Program 1488488488488444444444448444444444448444844484448488888488888044044044044
3Program 21620201325Program 21Program 2112112112112122122122122122122122122000000000001000100010001222222222223
4Program 3289238374391204272Program 317Program 3171734173417173417341734171717171734171717171734173434343434173434343434173434343434343434343434171717171717171717171717171734171734171734171734
5Program 4162218144244Program 42Program 4022022022022022222222222022202220222020202202022244424442444244444244444
6Program 512550109012515Program 55Program 5101010101010101010101015055555055555000005000005510510510510510510101010101010101010101015000500050005
7Program 610510515165360240Program 615Program 601501501515015015150150150151501501515000000000001501515151515151515151515303030303030303030303030151530151530151530151530
8Program 72504060160120180Program 710Program 72020202020202020202020300010001000100010010010010010010010101020101020101020101020101010101010101010101010102010201020102010201020
Sheet2 (2)
Cell Formulas
RangeFormula
U2:U8U2=Dashboard[Program]
V1:CO1V1=LET(cd,EDATE(DATE(SUBSTITUTE(Dashboard[[#Headers],[''23]],"'","")+2000,1,1),SEQUENCE(,COUNTA(Dashboard[[#Headers],[''23]:[''28]])*12,0)), cd )
V2:CO8V2=LET( y, DATE(SUBSTITUTE(Dashboard[[#Headers],[''23]:[''28]],"'","")+2000,1,1), cd,EDATE(DATE(SUBSTITUTE(Dashboard[[#Headers],[''23]],"'","")+2000,1,1),SEQUENCE(,COUNTA(Dashboard[[#Headers],[''23]:[''28]])*12,0)), m,MONTH(cd), s, BYCOL(cd,LAMBDA(x,FILTER(Dashboard[@[''23]:[''28]],YEAR(y)=YEAR(x)))), b, $O2, v,(INT((s/12*m)/b)-INT((s/12*(m-1))/b))*b, v )
Dynamic array formulas.
Click to expand...
Hi, that data is unfortunately not able to be a table since I use a unique formula and when I go to table, it creates a #SPill error.

But that data is currently in the following location: 'Data Tables'!A:B
 
Upvote 0
Where do you get a spill error?
Can you maybe upload the file without your data or with random sample data?
Trying to solve it like this is a bit difficult.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
New Dynamic Table
Replies
1
Views
160
Pestomania
P
K
  • Question
conditional formatting question
Replies
2
Views
161
kerm007
K
N
  • Solved
Let Function Failure
Replies
6
Views
539
Fluff
Fluff
Dexir
  • Solved
Countifs query
Replies
3
Views
185
Dexir
Dexir
M
  • Solved
Distribute Weekly Amount Monthly
Replies
3
Views
226
molopez
M

Forum statistics

Threads
1,215,072
Messages
6,122,966
Members
449,094
Latest member
Anshu121

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