Balance Work over multiple days (columns)

[tdownward]

In the attached spreadsheet, I am attempting to balance the workload (prod schedule column) over the next 5 columns (Mon - Fri). I have the formulas set up to divide the work evenly across each row, however, at the end of each day (bottom of each column) there is a total work for the day - the totals are much higher for Monday than they are for Friday due to the number of Prod scheduled (sometimes less than 5) I need to balance the work over the 5 days, so that at the bottom of the columns, the totals for the five days are evenly distributed across the week. Any ideas?P4 prod set up for forum.xls

[Ron Coderre]

I'm not sure how elegant this is, but it seems to get the job done.
Using your posted workbook...

• This ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)
determines which column is under-represented

Please Login or Register  to view this content.

Copy H3 and paste into H4:H36

• This regular formula begins the allocations

Please Login or Register  to view this content.

Copy that formula across and down through G36.

Using your posted data, these will be the end results for C37:G37

Please Login or Register  to view this content.

Is that something you can work with?
Copy H4 and Paste into

[tdownward]

I don't understand this part "This ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)
determines which column is under-represented" - at what point am I holding down ctrl and shift? I copied and pasted the formula into the spreadsheet as stated. See attached...Some definite errors occurring...Please advise.

[Kevin UK]

Hi

Enter your formula into the required cell/cells and on the keyboard, hold down the CTRL & SHIFT key, with them both held down, hit the enter key.

[Ron Coderre]

I don't understand this part "This ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)
determines which column is under-represented" - at what point am I holding down ctrl and shift? I copied and pasted the formula into the spreadsheet as stated. See attached...Some definite errors occurring...Please advise.

• Select cell H3
• Click in the formula...as if editing the cell
• Hold down CTRL and SHIFT while you press ENTER
Excel will but braces around the formula...DO NOT TYPE THEM IN YOURSELF.

Then copy that formula down.
Does that help?

[tdownward]

You are THE MAN! That is perfect & Awesome...all at the same time!! Thank you so much! You're a life saver!

[Ron Coderre]

You are THE MAN! That is perfect & Awesome...all at the same time!! Thank you so much! You're a life saver!

Thanks for the kind words...I'm glad I could help
(Plus: This one was fun!)