Divide cost across variable quantity

[berger_d]

Hi,

I'm trying to create a cost spreadsheet "tool". I've found some other solutions, but the Qty was always fixed and that is not the case for me. For example if an order has a bundle of 198 widgets that is sold for $72.40. I need to list the cost for each widget and if I simply divide the cost of $72.40 by 198, I get $0.37 each due to rounding. The preferred outcome is to have Qty 86 at $0.36 and Qty 112 at $0.37. Obviously if I didn't need to do this on a large number of items I'd just do it manually, but this is a regular issue I'm trying to solve with a formula.

I'd like to be able to enter the Cost in A1 and the Qty in A2 with a formula as needed. The widget cost and quantity are variable in each instance that I need this for. Ideally I'd like only two individual widget costs, but if a third would be needed to avoid rounding issues, that too would be acceptable.

 

[lrobbo314]

How about this?

Dashboard and Even Distribution of Items in Set.xlsm
	A	B	C	D	E	F
1	72.4
2	198		Qty:	86	112
3			Amt:	0.36	0.37
4
5			Total:	30.96	41.44	72.4
Sheet1

Cell Formulas
Range	Formula
D2:E3	D2	=LET(amt,A1,qty,A2,seq,SEQUENCE(qty),lb,TRUNC(amt/qty,2),ub,lb+0.01,lc,seq*lb,ic,ROUND((amt-lc)/ub,2),tbl,CHOOSE({1,2,3,4},seq,lc,ic,MOD(ic,INT(ic))=0),flt,FILTER(tbl,INDEX(tbl,,4)),mx,INDEX(flt,,1)*INDEX(flt,,3),m,MAX(mx),nt,CHOOSE({1,1,1,2},flt,mx),x,INDEX(FILTER(nt,INDEX(nt,,4)=m),,{1,3}),IFERROR(INDEX(x,SEQUENCE(ROWS(x)+1),{1,2}),CHOOSE({1,2},lb,ub)))
D5:E5	D5	=D2*D3
F5	F5	=SUM(D5:E5)
Dynamic array formulas.

 

[berger_d]

How about this?

Dashboard and Even Distribution of Items in Set.xlsm
	A	B	C	D	E	F
1	72.4
2	198		Qty:	86	112
3			Amt:	0.36	0.37
4
5			Total:	30.96	41.44	72.4
Sheet1

Cell Formulas
Range	Formula
D2:E3	D2	=LET(amt,A1,qty,A2,seq,SEQUENCE(qty),lb,TRUNC(amt/qty,2),ub,lb+0.01,lc,seq*lb,ic,ROUND((amt-lc)/ub,2),tbl,CHOOSE({1,2,3,4},seq,lc,ic,MOD(ic,INT(ic))=0),flt,FILTER(tbl,INDEX(tbl,,4)),mx,INDEX(flt,,1)*INDEX(flt,,3),m,MAX(mx),nt,CHOOSE({1,1,1,2},flt,mx),x,INDEX(FILTER(nt,INDEX(nt,,4)=m),,{1,3}),IFERROR(INDEX(x,SEQUENCE(ROWS(x)+1),{1,2}),CHOOSE({1,2},lb,ub)))
D5:E5	D5	=D2*D3
F5	F5	=SUM(D5:E5)
Dynamic array formulas.

WOW This looks exactly like what I need!!!! And in about 15 minutes after I posted the request. Very very grateful for your help.

I’ll reply back after I’ve done some testing.

 

[berger_d]

How about this?

Dashboard and Even Distribution of Items in Set.xlsm
	A	B	C	D	E	F
1	72.4
2	198		Qty:	86	112
3			Amt:	0.36	0.37
4
5			Total:	30.96	41.44	72.4
Sheet1

Cell Formulas
Range	Formula
D2:E3	D2	=LET(amt,A1,qty,A2,seq,SEQUENCE(qty),lb,TRUNC(amt/qty,2),ub,lb+0.01,lc,seq*lb,ic,ROUND((amt-lc)/ub,2),tbl,CHOOSE({1,2,3,4},seq,lc,ic,MOD(ic,INT(ic))=0),flt,FILTER(tbl,INDEX(tbl,,4)),mx,INDEX(flt,,1)*INDEX(flt,,3),m,MAX(mx),nt,CHOOSE({1,1,1,2},flt,mx),x,INDEX(FILTER(nt,INDEX(nt,,4)=m),,{1,3}),IFERROR(INDEX(x,SEQUENCE(ROWS(x)+1),{1,2}),CHOOSE({1,2},lb,ub)))
D5:E5	D5	=D2*D3
F5	F5	=SUM(D5:E5)
Dynamic array formulas.

Hi @lrobbo314

Thanks again for the very quick reply yesterday. I do not have the XL2BB add in, but don't believe I need it to copy/paste your mini sheet into Excel. When I copy/paste into Excel (365) I'm getting a #NAME? error in D2. Do I need to do anything else to utilize the formula you provided? Sorry, I'm sure this is user error on my part, I've searched the forum but not seeing a solution to this.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E
1	72.40
2	198		112	86
3			0.37	0.36
4
5			41.44	30.96	72.40
Lists

Cell Formulas
Range	Formula
C2	C2	=(A1-ROUNDDOWN(A1/A2,2)*A2)/0.01
D2	D2	=A2-C2
C3	C3	=ROUNDUP(A1/A2,2)
D3	D3	=ROUNDDOWN(A1/A2,2)
C5:D5	C5	=C2*C3
E5	E5	=SUM(C5:D5)

 

[berger_d]

How about

+Fluff 1.xlsm
	A	B	C	D	E
1	72.40
2	198		112	86
3			0.37	0.36
4
5			41.44	30.96	72.40
Lists

Cell Formulas
Range	Formula
C2	C2	=(A1-ROUNDDOWN(A1/A2,2)*A2)/0.01
D2	D2	=A2-C2
C3	C3	=ROUNDUP(A1/A2,2)
D3	D3	=ROUNDDOWN(A1/A2,2)
C5:D5	C5	=C2*C3
E5	E5	=SUM(C5:D5)

@Fluff This seems to work perfectly!!!!! Thank you for your help.

 

[Fluff]

Glad we could help & thanks for the feedback.