Post calculated result based on drop down text selected

[r71lima]

Hopefully this will make sense.

My worksheet is to be a list of purchased items that are split between 3 people. Only one person will make the full purchase but then the other two will be responsible for paying that person back their 1/3 of the rest of the cost.

I have chosen to use a drop down list where only the first initials of each of the three people are in the list. The initial of the person that paid for all of the purchase can be chosen and I hoped to have a way to have the total amount split into thirds but only the result of that 1/3rd of the total is attributed to the names who DID NOT already pay. Column G has the drop down list of initials that is hidden in column O3 through O5. The calculation of the total divided by 3 is located in L1. I'm not sure how to handle to remaining amounts that cannot be divisible by 3 yet.

I've tried to attach what I have done so far. Thank you so much in advance for any time anyone is willing to help me resolve this task.

Still.xlsx
	A	B	C	D	E	F	G	H	I	J	K					P
1
2	DATE	PRODUCT DESCRIPTION	COST	TAX	SHIPPING	TOTAL	PD	MATT	STEVE	TERRY
3	5/31/22	Suteck Alcohol Still 8 Gal Stainless Steel	$159.99	$10.16		$170.15	T	$56.72
4						$0.00
5						$0.00
6						$0.00
7						$0.00
8						$0.00
9						$0.00
10						$0.00
11						$0.00
12						$0.00
13						$0.00
Sheet1

Cell Formulas
Range	Formula
H3	H3	=IF(G3="T",$F3/$L$1,"0")
F3:F13	F3	=SUM(C3:E3)

Cells with Data Validation
Cell	Allow	Criteria
G3:G13	List	=$O$2:$O$5

 

[KRice]

Is there a reason for splitting the cost of each item? A simpler approach is to track who paid for which item, which you do in column G with initials. Then determine the total paid by each individual and the total paid altogether. Divide the total paid altogether by 3 to determine everyone's even "fair" share. Then compare the fair share amount to what that individual has paid to determine whether they need to pay money into the pot for distribution to the others, or take money from the pot to reimburse them for their overpayment (as shown in green).

MrExcel_20220610.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1									Total->	$ 727.27
2	DATE	PRODUCT DESCRIPTION	COST	TAX	SHIPPING	TOTAL	PD			Amt Each Has Paid	Fair Share	Pay into (+) or take from (-) the pot
3	5/31/2022	Item 1	160	10.2		170.15	T		Matt	$ 303.00	$ 242.42	$ (60.58)
4		Item 2				100.00	M		Steve	$ 209.11	$ 242.42	$ 33.31
5		Item 3				33.00	S		Terry	$ 215.16	$ 242.42	$ 27.26
6		Item 4				75.00	S
7		Item 5				90.00	S
8		Item 6				11.00	M
9		Item 7				5.50	T
10		Item 8				12.75	T
11		Item 9				192.00	M
12		Item 10				11.11	S
13		Item 11				26.76	T
Sheet6

Cell Formulas
Range	Formula
J1	J1	=SUM(J3:J5)
F3	F3	=SUM(C3:E3)
J3:J5	J3	=SUMIF($G$3:$G$13,LEFT($I3,1),$F$3:$F$13)
K3:K5	K3	=ROUND(SUM($J$3:$J$5/(COUNTA($I$3:$I$5))),2)
L3:L5	L3	=K3-J3

 

[KRice]

I should have mentioned...after calculating the amt each has paid with the J3 formula, you could jump directly to the answer by using the M3 formula, and skip the other two columns (K:L).

MrExcel_20220610.xlsx
	I	J	K	L	M
2		Amt Each Has Paid	Fair Share	Pay into (+) or take from (-) the pot
3	Matt	$ 303.00	$ 242.42	$ (60.58)	$ (60.58)
4	Steve	$ 209.11	$ 242.42	$ 33.31	$ 33.31
5	Terry	$ 215.16	$ 242.42	$ 27.26	$ 27.26
Sheet6

Cell Formulas
Range	Formula
J3:J5	J3	=SUMIF($G$3:$G$13,LEFT($I3,1),$F$3:$F$13)
K3:K5	K3	=ROUND(SUM($J$3:$J$5/(COUNTA($I$3:$I$5))),2)
L3:L5	L3	=K3-J3
M3:M5	M3	=AVERAGE($J$3:$J$5)-J3

 

[r71lima]

Is there a reason for splitting the cost of each item? A simpler approach is to track who paid for which item, which you do in column G with initials. Then determine the total paid by each individual and the total paid altogether. Divide the total paid altogether by 3 to determine everyone's even "fair" share. Then compare the fair share amount to what that individual has paid to determine whether they need to pay money into the pot for distribution to the others, or take money from the pot to reimburse them for their overpayment (as shown in green).

MrExcel_20220610.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1									Total->	$ 727.27
2	DATE	PRODUCT DESCRIPTION	COST	TAX	SHIPPING	TOTAL	PD			Amt Each Has Paid	Fair Share	Pay into (+) or take from (-) the pot
3	5/31/2022	Item 1	160	10.2		170.15	T		Matt	$ 303.00	$ 242.42	$ (60.58)
4		Item 2				100.00	M		Steve	$ 209.11	$ 242.42	$ 33.31
5		Item 3				33.00	S		Terry	$ 215.16	$ 242.42	$ 27.26
6		Item 4				75.00	S
7		Item 5				90.00	S
8		Item 6				11.00	M
9		Item 7				5.50	T
10		Item 8				12.75	T
11		Item 9				192.00	M
12		Item 10				11.11	S
13		Item 11				26.76	T
Sheet6

Cell Formulas
Range	Formula
J1	J1	=SUM(J3:J5)
F3	F3	=SUM(C3:E3)
J3:J5	J3	=SUMIF($G$3:$G$13,LEFT($I3,1),$F$3:$F$13)
K3:K5	K3	=ROUND(SUM($J$3:$J$5/(COUNTA($I$3:$I$5))),2)
L3:L5	L3	=K3-J3

Only one of the partners will be sent to the store so the other two would have to pay their one third after the purchase

 

[r71lima]

I should have mentioned...after calculating the amt each has paid with the J3 formula, you could jump directly to the answer by using the M3 formula, and skip the other two columns (K:L).

MrExcel_20220610.xlsx
	I	J	K	L	M
2		Amt Each Has Paid	Fair Share	Pay into (+) or take from (-) the pot
3	Matt	$ 303.00	$ 242.42	$ (60.58)	$ (60.58)
4	Steve	$ 209.11	$ 242.42	$ 33.31	$ 33.31
5	Terry	$ 215.16	$ 242.42	$ 27.26	$ 27.26
Sheet6

Cell Formulas
Range	Formula
J3:J5	J3	=SUMIF($G$3:$G$13,LEFT($I3,1),$F$3:$F$13)
K3:K5	K3	=ROUND(SUM($J$3:$J$5/(COUNTA($I$3:$I$5))),2)
L3:L5	L3	=K3-J3
M3:M5	M3	=AVERAGE($J$3:$J$5)-J3

The men are only using this sheet to know how much money they need to pay the person who purchased all of the items at the store. Later I believe they will probably want something more complicated or indepth in order to know profit versus cost etc... Right now it appears to only be about finding out how much each of them should pay the person that made the purchase at the store for each event.

 

[KRice]

That seems a bit more complicated, but on a transaction-by-transaction basis, this is what it would look like:

MrExcel_20220610.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1								242.42	242.42	242.42	Net out of pocket
2								202.00	139.41	143.44	Total received from others
3								303.00	209.11	215.16	Total paid for items directly out of pocket
4						727.27		141.42	172.72	170.70	Total reimbursed to someone else		Total->	$ 727.27
5	DATE	PRODUCT DESCRIPTION	COST	TAX	SHIPPING	TOTAL	PD	M	S	T	Table below left shows amounts owed to purchaser (who is indicated with 0)			Amt Each Has Paid	Fair Share	Pay into (+) or take from (-) the pot
6	5/31/2022	Item 1	160	10.2		170.15	T	56.72	56.72	0.00			Matt	$ 303.00	$ 242.42	$ (60.58)	$ (60.58)
7		Item 2				100.00	M	0.00	33.33	33.33			Steve	$ 209.11	$ 242.42	$ 33.31	$ 33.31
8		Item 3				33.00	S	11.00	0.00	11.00			Terry	$ 215.16	$ 242.42	$ 27.26	$ 27.26
9		Item 4				75.00	S	25.00	0.00	25.00
10		Item 5				90.00	S	30.00	0.00	30.00
11		Item 6				11.00	M	0.00	3.67	3.67
12		Item 7				5.50	T	1.83	1.83	0.00
13		Item 8				12.75	T	4.25	4.25	0.00
14		Item 9				192.00	M	0.00	64.00	64.00
15		Item 10				11.11	S	3.70	0.00	3.70
16		Item 11				26.76	T	8.92	8.92	0.00
Sheet6

Cell Formulas
Range	Formula
H1:J1	H1	=H3+H4-H2
H2:J2	H2	=SUM(($H$6:$J$16)*($G$6:$G$16=H$5))
H3:J3	H3	=SUMIF($G$6:G$16,H$5,$F$6:$F$16)
H4:J4,F4	H4	=SUM(H6:H16)
N4	N4	=SUM(N6:N8)
F6	F6	=SUM(C6:E6)
H6:J16	H6	=IF(H$5=$G6,0,$F6/COUNTA($H$5:$J$5))
N6:N8	N6	=SUMIF($G$6:$G$16,LEFT($M6,1),$F$6:$F$16)
O6:O8	O6	=ROUND(SUM($N$6:$N$8/(COUNTA($M$6:$M$8))),2)
P6:P8	P6	=O6-N6
Q6:Q8	Q6	=AVERAGE($N$6:$N$8)-N6

 

[r71lima]

That seems a bit more complicated, but on a transaction-by-transaction basis, this is what it would look like:

MrExcel_20220610.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1								242.42	242.42	242.42	Net out of pocket
2								202.00	139.41	143.44	Total received from others
3								303.00	209.11	215.16	Total paid for items directly out of pocket
4						727.27		141.42	172.72	170.70	Total reimbursed to someone else		Total->	$ 727.27
5	DATE	PRODUCT DESCRIPTION	COST	TAX	SHIPPING	TOTAL	PD	M	S	T	Table below left shows amounts owed to purchaser (who is indicated with 0)			Amt Each Has Paid	Fair Share	Pay into (+) or take from (-) the pot
6	5/31/2022	Item 1	160	10.2		170.15	T	56.72	56.72	0.00			Matt	$ 303.00	$ 242.42	$ (60.58)	$ (60.58)
7		Item 2				100.00	M	0.00	33.33	33.33			Steve	$ 209.11	$ 242.42	$ 33.31	$ 33.31
8		Item 3				33.00	S	11.00	0.00	11.00			Terry	$ 215.16	$ 242.42	$ 27.26	$ 27.26
9		Item 4				75.00	S	25.00	0.00	25.00
10		Item 5				90.00	S	30.00	0.00	30.00
11		Item 6				11.00	M	0.00	3.67	3.67
12		Item 7				5.50	T	1.83	1.83	0.00
13		Item 8				12.75	T	4.25	4.25	0.00
14		Item 9				192.00	M	0.00	64.00	64.00
15		Item 10				11.11	S	3.70	0.00	3.70
16		Item 11				26.76	T	8.92	8.92	0.00
Sheet6

Cell Formulas
Range	Formula
H1:J1	H1	=H3+H4-H2
H2:J2	H2	=SUM(($H$6:$J$16)*($G$6:$G$16=H$5))
H3:J3	H3	=SUMIF($G$6:G$16,H$5,$F$6:$F$16)
H4:J4,F4	H4	=SUM(H6:H16)
N4	N4	=SUM(N6:N8)
F6	F6	=SUM(C6:E6)
H6:J16	H6	=IF(H$5=$G6,0,$F6/COUNTA($H$5:$J$5))
N6:N8	N6	=SUMIF($G$6:$G$16,LEFT($M6,1),$F$6:$F$16)
O6:O8	O6	=ROUND(SUM($N$6:$N$8/(COUNTA($M$6:$M$8))),2)
P6:P8	P6	=O6-N6
Q6:Q8	Q6	=AVERAGE($N$6:$N$8)-N6

Thankyou so much for your help.

 

[KRice]

You're welcome...I'm happy to help.