fbpx
Search
Close this search box.

Can you split “The Hangover” expenses? [Excel Homework]

Share

Facebook
Twitter
LinkedIn

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.

output-format-hangover-expenses

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:

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

9 Responses to “Can you split “The Hangover” expenses? [Excel Homework]”

  1. Denys Calvin says:

    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.

  2. Iliya says:

    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

    • Yana says:

      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

  3. David N says:

    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.

    • David N says:

      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))

  4. Miss Fig says:

    More of these please! Fun challenge and enjoyed seeing how you & others solved it.

  5. sam says:

    =MAP(G2#,
    LAMBDA(r,
    SUM(
    --TEXTSPLIT(
    TEXTJOIN(",",1,
    FILTER(
    TEXTAFTER(D[Expense Details],":"),
    TEXTBEFORE(D[Expense Details],":")=r)
    ),
    ",")
    )
    )
    )

  6. Gelu says:

    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

  7. Jon says:

    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)

Leave a Reply