One of my all time favorite comedies is The Hangover. It is the story of four friends – Alan, Stu, Doug & Phil going to Las Vegas for an (un)forgettable night of adventure and mayhem. While the movie did end on a happy note, they forgot one little thing.
They didn’t share the expenses!
So in this installment of Excel Homework, can you split the expenses?
The Problem
Assuming you have below data (feel free to copy paste it in to your Excel, or use this workbook):
Expense Details
Phil: 255.12,121.29,160.23
Alan: 43.17,226.76,343.66,358.01
Phil: 159.95,177.56,278.37
Stu: 206.51,188.09,244.68,33.21,152.98
Phil: 285.91,306.4,140.68
Doug: 226.76,226.76,169.6,208.67
Phil: 326.4
Phil: 308.43,224.95,102.33
Phil: 168.64
Stu: 259.93
Stu: 121.29
Stu: 177
Alan: 172.23
Alan: 214.04,162.58,205.66,188.16,128.41
Alan: 62.75,160.51,173.34,283.8
Phil: 356.3,361.34,41.35,255.94,67.42
Stu: 168.25,161.28,248.45
Doug: 163.93,308.43,218.58
Phil: 226.04,265.97,140.81,197.45
Doug: 216.79,180.39,246.65,149.55
Your need to generate a a table like this to show who needs to pay whom & how much.
Get cracking then!
Sample file & Your Solution:
Click here to download the sample file with the expenses data.
You can use any Excel feature to come up with the answer.
Tell me how you solved it using the comments box below 👇
Watch my solution:
I made a video about this problem and one approach to solve it (using Power Query and Dynamic Array Formulas). Check it out below or on my Channel.
More homework problems:
Need a challenge? I got one or many for you:
9 Responses to “Can you split “The Hangover” expenses? [Excel Homework]”
Data copied into cells A1:A20 in new workbook.
Created new name "ev" (in cell B1) that contains following formula in "Refers to" box:
"=EVALUATE(SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND(":",A1)-1),",","+"))"
Set cells B1:B20 to "=ev" (resulting their each containing the total expenses in the corresponding cell in column A)
List 4 names in cells C1:C4.
Set cell D1 to "=SUMIF(A:A,C1&"*",B:B)", and copy down to D2:D4; to calculate total paid by each friend.
Hi,
all job you can be done in Power query.
After you split, just Group by name and sum values.
Then duplicate query drill down column with values next -> from "Statistics" hit SUM (if want name it).
Back to grouped query, add custom column -> use SUM (given name) from previous step divide to 4 and subtracts column with each person values and ... ready.
Regards
You can add a custom column, no need to duplicate the query
= Table.AddColumn(#"Renamed Columns", "Share of expenses", each List.Sum(#"Renamed Columns"[Paid])/4)
Renamed Columns is the name of my previous step
Could get a spill array of names using:
=SORT(UNIQUE(LEFT(exp[Expense Details],FIND(":",exp[Expense Details])-1)))
FILTERXML won't work with a spill reference to the list of names (i.e. G7# won't work), so I can only figure out how to do those one by one:
=SUM(FILTERXML(""&CONCAT(LET(data,exp[Expense Details],psn,LEFT(data,FIND(":",data)-1),beg,"",end,"",chr,FIND(":",data),beg&SUBSTITUTE(REPLACE(data,1,chr+1,""),",",end&beg)&end))&"","//"&G7))
Then each share is the sum of the four totals divided by four with each receive/pay being the difference between the share and that person's total.
Sorry, I forgot to account for HTML tags. Hopefully the formula renders correctly this time:
=SUM(FILTERXML("<list>"&CONCAT(LET(data,exp[Expense Details],psn,LEFT(data,FIND(":",data)-1),beg,"<"&psn&">",end,"</"&psn&">",chr,FIND(":",data),beg&SUBSTITUTE(REPLACE(data,1,chr+1,""),",",end&beg)&end))&"</list>","//"&G7))
More of these please! Fun challenge and enjoyed seeing how you & others solved it.
=MAP(G2#,
LAMBDA(r,
SUM(
--TEXTSPLIT(
TEXTJOIN(",",1,
FILTER(
TEXTAFTER(D[Expense Details],":"),
TEXTBEFORE(D[Expense Details],":")=r)
),
",")
)
)
)
Hi Chandoo,
Thank you for this example. It got me started with PQ... How would you do the opposite of this, if I may ask?
From this:
Source H1 H2
S1 a b
S1 a2 b2
S2 c d
S2 c2 d2
S3 e f
S3 e2 f2
To this:
S1 S1 S2 S2 S3 S3
a b c d e f
a2 b2 c2 d2 e2 f2
Thank you,
G
Hi Chandoo,
I love these fun challenges - Keep them coming.
Managed to complete with a single formula:
=LET(
all,$C$7:$C$26,
nm,TEXTBEFORE(all,":"),
unm,UNIQUE(nm),
dt,TRIM(RIGHT(all,LEN(all)-FIND(":",all))),
am,MAP(dt,LAMBDA(r,
SUM(FILTERXML(""&SUBSTITUTE(r,",",
"")&
"",
"//d")))),
tot,MAP(unm,LAMBDA(x,SUMPRODUCT(--(
nm=x)*(am)))),
share,SUM(tot)/4,
soe,VSTACK(share,share,share,share),
trp,tot-share,
hd,HSTACK("Person","How Much Spent","Share of Exp","Receive / Pay"),
tb,HSTACK(unm,tot,soe,trp),
end,VSTACK(hd,tb),
end)