Value Distribution across columns without exceeding value

[dcalderwood]

I have been searching and searching and I have found a lot of great formulas, but none of them get me quite where I want to be. I have a worksheet that has every project we are working listed, it includes:
Column C: Start Date - let's say 4/15/2023
Column D: Number of weeks to complete - let's say 12
Column E: Calculated end date - with the examples we would see 7/8/2023
Column F: Number of hours for each department - for example Project Management would have 35 in this column
Column G forward each have a date of the week start

Row 1 is the calculated weeknum value, I'm using that in my current formula, so end date really isn't necessary:
=IF(ISBLANK($D3),"",(($F4/$D3)*AND(L$1<=(WEEKNUM($C$3,1)+$D3))))

I want to be able to distribute the 35 PM hours across all of the weeks within the date range, but I don't want it to exceed the 35 hours. I have managed to get it to distribute evenly, but I need whole number hours, and if there are an off number of hours remaining I want it to show that. For instance, the above examples provides a value of 2.91666667 to each column. If I round to 3, I end up with 36 weeks. So what I would like for it to do is distribute 3 across, and then the final week would have 2 hours. I hope this makes sense and I hope you can help. Thank you so much!

Current Formula

 

[Anfinsen]

Not sure this will get you where you need to, but what if you floor the math on every formula, except for the last, ceiling that one.
You would be more likely to get a better response if you posted an example sheet using Xl2BB.

 

[dcalderwood]

Not sure this will get you where you need to, but what if you floor the math on every formula, except for the last, ceiling that one.
You would be more likely to get a better response if you posted an example sheet using Xl2BB.

Unfortunately I don't think that will work as I'm not putting the formula in the specific columns I want the same formula across all columns, and have it know where to put the numbers. Which is the part that is working as it should.

 

[dcalderwood]

Unfortunately I don't think that will work as I'm not putting the formula in the specific columns I want the same formula across all columns, and have it know where to put the numbers. Which is the part that is working as it should.

Actually that formula is not working as I thought. I'm fixing that part now, and may have figured out my own question. I will update if I have it worked out.

 

[Anfinsen]

Actually that formula is not working as I thought. I'm fixing that part now, and may have figured out my own question. I will update if I have it worked out.

Hope you get it figured out! In the future, if you upload and example sheet using XL2BB, you are more likely to get a better response.

Cheers!

 

[dcalderwood]

OK, my apologies, I now have it working the way it should for the population evenly distributed, but still not figuring out how to make it essentially deduct as it populate and end with a final value that doesn't exceed the total.

New formula I'm working with is:
=IF(AND(G$1>=(WEEKNUM($C$3,1)),(G$1<=(WEEKNUM($E$3,1)))),$F4/$D3,"")

 

[Anfinsen]

Without an example worksheet, We can't really help you. Here is an example of an XL2BB mini sheet, with some formulas i added in, making some assumptions on your data and it's placement, Hopefully this will send you in the right direction.

D Calderwood.xlsm
	H
11
Sheet1

 

[dcalderwood]

Hope you get it figured out! In the future, if you upload and example sheet using XL2BB, you are more likely to get a better response.

Cheers!

Forecast Dashboard.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU
1							13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45	46	47	48	49	50	51	52	53
2	Client	Project	Start Date	Duration (Weeks)	End Date	SOW Hours	3/26/2023	4/2/2023	4/9/2023	4/16/2023	4/23/2023	4/30/2023	5/7/2023	5/14/2023	5/21/2023	5/28/2023	6/4/2023	6/11/2023	6/18/2023	6/25/2023	7/2/2023	7/9/2023	7/16/2023	7/23/2023	7/30/2023	8/6/2023	8/13/2023	8/20/2023	8/27/2023	9/3/2023	9/10/2023	9/17/2023	9/24/2023	10/1/2023	10/8/2023	10/15/2023	10/22/2023	10/29/2023	11/5/2023	11/12/2023	11/19/2023	11/26/2023	12/3/2023	12/10/2023	12/17/2023	12/24/2023	12/31/2023
3	BJI	AD Assessment and Remediation	4/15/2023	12	7/8/2023
4		Project Management				35			2.91667	2.91667	2.91667	2.91667	2.91667	2.91667	2.91667	2.91667	2.91667	2.91667	2.91667	2.91667	2.91667
5		Operations				14			1.16667	1.16667	1.16667	1.16667	1.16667	1.16667	1.16667	1.16667	1.16667	1.16667	1.16667	1.16667	1.16667
6		Network/Security				25			2.08333	2.08333	2.08333	2.08333	2.08333	2.08333	2.08333	2.08333	2.08333	2.08333	2.08333	2.08333	2.08333
Forecasting

Cell Formulas
Range	Formula
G1:AU1	G1	=WEEKNUM(G2,1)
E3	E3	=IF(C3,C3+(D3*7),"")
G4:AU4	G4	=IF(AND(G$1>=(WEEKNUM($C$3,1)),(G$1<=(WEEKNUM($E$3,1)))),$F4/$D3,"")
G5:AU5	G5	=IF(AND(G$1>=(WEEKNUM($C$3,1)),(G$1<=(WEEKNUM($E$3,1)))),$F5/$D3,"")
G6:AU6	G6	=IF(AND(G$1>=(WEEKNUM($C$3,1)),(G$1<=(WEEKNUM($E$3,1)))),$F6/$D3,"")

Named Ranges
Name	Refers To	Cells
solver_adj	=Forecasting!$F$4	G4:AU4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G4:AU6	Cell	does not contain a blank value	text	NO

 

[Dave Patton]

The Start date should be consistent with G2:
The formula should be =IF(AND(G$1>=(WEEKNUM($C$3,1)),(G$1<(WEEKNUM($E$3,1)))),$F4/$D3,"")

T202304a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1							13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28
2	Client	Project	Start Date	Duration (Weeks)	End Date	SOW Hours	26-03-23	02-04-23	Sun 09-04-23	16-04-23	23-04-23	30-04-23	07-05-23	14-05-23	21-05-23	28-05-23	04-06-23	11-06-23	18-06-23	25-06-23	02-07-23	09-07-23
3	0	AD Assessment and Remediation	Sun 16-Apr-23	12	09-07-23
4		Project Management				35				2.91666667	2.9166667	2.9166667	2.9166667	2.9166667	2.9166667	2.9166667	2.9166667	2.9166667	2.9166667	2.9166667	2.9166667
5		Operations				14				1.16666667	1.1666667	1.1666667	1.1666667	1.1666667	1.1666667	1.1666667	1.1666667	1.1666667	1.1666667	1.1666667	1.1666667
6		Network/Security				25				2.08333333	2.0833333	2.0833333	2.0833333	2.0833333	2.0833333	2.0833333	2.0833333	2.0833333	2.0833333	2.0833333	2.0833333
7
4b

Cell Formulas
Range	Formula
G1:V1	G1	=WEEKNUM(G2,1)
E3	E3	=IF(C3,C3+(D3*7),"")
G4:V4	G4	=IF(AND(G$1>=(WEEKNUM($C$3,1)),(G$1<(WEEKNUM($E$3,1)))),$F4/$D3,"")
G5:V5	G5	=IF(AND(G$1>=(WEEKNUM($C$3,1)),(G$1<(WEEKNUM($E$3,1)))),$F5/$D3,"")
G6:V6	G6	=IF(AND(G$1>=(WEEKNUM($C$3,1)),(G$1<(WEEKNUM($E$3,1)))),$F6/$D3,"")

 

[dcalderwood]

The Start date should be consistent with G2:
The formula should be =IF(AND(G$1>=(WEEKNUM($C$3,1)),(G$1<(WEEKNUM($E$3,1)))),$F4/$D3,"")

T202304a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1							13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28
2	Client	Project	Start Date	Duration (Weeks)	End Date	SOW Hours	26-03-23	02-04-23	Sun 09-04-23	16-04-23	23-04-23	30-04-23	07-05-23	14-05-23	21-05-23	28-05-23	04-06-23	11-06-23	18-06-23	25-06-23	02-07-23	09-07-23
3	0	AD Assessment and Remediation	Sun 16-Apr-23	12	09-07-23
4		Project Management				35				2.91666667	2.9166667	2.9166667	2.9166667	2.9166667	2.9166667	2.9166667	2.9166667	2.9166667	2.9166667	2.9166667	2.9166667
5		Operations				14				1.16666667	1.1666667	1.1666667	1.1666667	1.1666667	1.1666667	1.1666667	1.1666667	1.1666667	1.1666667	1.1666667	1.1666667
6		Network/Security				25				2.08333333	2.0833333	2.0833333	2.0833333	2.0833333	2.0833333	2.0833333	2.0833333	2.0833333	2.0833333	2.0833333	2.0833333
7
4b

Cell Formulas
Range	Formula
G1:V1	G1	=WEEKNUM(G2,1)
E3	E3	=IF(C3,C3+(D3*7),"")
G4:V4	G4	=IF(AND(G$1>=(WEEKNUM($C$3,1)),(G$1<(WEEKNUM($E$3,1)))),$F4/$D3,"")
G5:V5	G5	=IF(AND(G$1>=(WEEKNUM($C$3,1)),(G$1<(WEEKNUM($E$3,1)))),$F5/$D3,"")
G6:V6	G6	=IF(AND(G$1>=(WEEKNUM($C$3,1)),(G$1<(WEEKNUM($E$3,1)))),$F6/$D3,"")

Thank you but that's not what I'm looking for. The start dates are determined by the project and entered based on planning for the project. The spreadsheet needs to automatically forecast the number of hours for each team, based on the start date, and total number of hours. I need it to distribute the hours within the timeline of the project. So that is working fine. My need is for it not to distribute them but not exceed the total hours. As in 3, 3, 3, 3, 3, 2, instead of 3, 3, 3, 3, 3, 3, which is more than that allowed hours in the project.