Forum Discussion

JLaz86's avatar
JLaz86
Copper Contributor
Jun 30, 2022

Assigning values from totals to random cells in a given range

Good afternoon,

 

I have a fun little problem I'm trying to solve. I am trying to assign values to cells from object totals.

 

I have 12 grants I'm working on, and each grant has a total grant number of hours per week. So I want to assign those values to random days without going over the 8-hour workday. 

 

I attached a picture below as an example of how it could look. 

 

Please let me know if you have any ideas.

 

Thanks,

Joseph Lazazzera

 

 

excel
Formulas and Functions
training

2 Replies

  • ecovonrein's avatar
    ecovonrein
    Iron Contributor
    Jul 01, 2022
    If I understand well, then this is necessarily a circular problem. Random allocation moves total hours, influences next random allocation ... I do not like programming circular references but in this specific case, you can enable Iterative Calculations under File/Options/Formulas. After you check that, Excel will play around until it finds your solution.
    • mtarler's avatar
      mtarler
      Silver Contributor
      Jul 01, 2022

      JLaz86 ok "fun" turned into frustrating very quickly but here is what I got:

      =LET(in,L3:L14,
n, ROWS(in),
dayCount,10,
totalSlots,dayCount*8*4,
rslots,INDEX(SORT(MAKEARRAY(totalSlots,2,LAMBDA(rr,cc,IF(cc=1,rr,RAND()))),2),,1),
cumHrList,SCAN(0,in,LAMBDA(**bleep**,i,**bleep**+i*4)),
rList,IFERROR(XMATCH(SEQUENCE(totalSlots),cumHrList,1),""),
MAKEARRAY(n,dayCount,LAMBDA(r,c,SUM(FILTER(--(rslots=rslots),(rList=r)*(rslots>(c-1)*8*4)*(rslots<=c*8*4),0))/4))
)

      In the attached I give you 2 tables.  The 1st uses the formula above to create a randomized grid.  But since I always hate there random grids because they change all the time and you have no control over it I give you the second grid that does that same thing but uses a PRNG function I wrote so you can select a 'seed' value and based on that seed value it creates the 'random' grid.  change the seed and see a new 'random' grid or go back to a previous seed to see that grid again.  Link that seed to the date and or time for automatic updates...