Split expenses calculator
This article demonstrates two ways to calculate expenses evenly split across multiple people. The first one is a formula solution, see image above and the second one is a VBA macro solution.
What's on this webpage
1. How to split expenses evenly (Formula)
Everything is calculated automatically, the only thing you need to enter is the expenses each person has, see Excel Table in the image above. The date and Expenses columns are not really needed for the calculations.
The formulas in cell G3:H3, and I3 calculates how much each person needs to pay and to who to split expenses evenly based on the amounts in the Excel Table.
Formula in cell G3:
Formula in cell H3:
Formula in cell I3:
The formulas above contain Excel 365 functions and work only in Excel 365, they use values from columns K to M, see image below.
Dynamic array formula in cell K3:
Formula in cell L3:
Formula in cell M3:
2. How to split expenses evenly (VBA Macro)
This workbook lets you split expenses evenly with other people. Type name, expense, and amount in the Excel table on sheet 'Expenses'.
Excel returns amounts to be paid and individuals involved. This is not the macro you are looking for if you want to calculate the smallest number of transactions possible.
How calculation sheet works
The vba macro uses the values in cell range D1:E5 and calculates transactions so all sums even out.
For example, Tom pays Fred $84.177 and his sum is 84.177 - 84.177 = 0. Hank pays Fred $20.92, Ted $26.87, Martin $25.08 and his sum is 72.88 - 20.92 - 26.87 - 25.08 = 0.
Those were the necessary transactions to even out all user sums to zero. The remaining sums are now all 0, Martin's sum is -25.08 + 25.08 = 0, Ted -26.87 + 26.87 = 0 and Fred -105.098 + 84.177 + 20.92 = 0.
How I made this workbook
There are two sheets in this workbook, 'Expenses' and 'Calculation'. Here are the formulas on 'Calculation' sheet.
Unique distinct names in column A:
Want to know more about this array formula?
Read this post: How to extract a unique distinct list from a column
Sum values for each unique name in column B:
Count unique names in cell H1:
Sum amounts in cell G1:
VBA macro
'Name macro Sub SplitExp() 'Dimension variables and declare data types Dim r As Single, d As Single, e As Single, Lrow As Single 'Disable screen refresh Application.ScreenUpdating = False 'With ... End With statement With Worksheets("Calculation") 'Save value in cell H1 to variable r r = .Range("H1") 'Clear everything in columns D to E .Columns("D:E").Clear 'Clear cell range F2:H100 in worksheet Expenses Worksheets("Expenses").Range("F2:H100").Clear 'Save values from cell range A2:Br+1 to cell range D1:Er based on variable r .Range("D" & 1 & ":E" & r) = .Range("A" & 2 & ":B" & r + 1).Value 'For ... Next statement For d = 1 To r .Range("E" & d) = (.Range("G1") / r) - .Range("E" & d) Next d .Columns("D:E").Sort key1:=.Range("E1"), order1:=xlDescending, Header:=xlNo For d = 1 To r For e = r To 1 Step -1 Lrow = Worksheets("Expenses").Range("F" & Rows.Count).End(xlUp).Row + 1 If Round(.Range("E" & d), 2) <> 0 And Round(.Range("E" & e), 2) <> 0 Then If Application.Min(Abs(.Range("E" & d)), Abs(.Range("E" & e))) = Abs(.Range("E" & d)) Then Worksheets("Expenses").Range("F" & Lrow & ":H" & Lrow) = Array(.Range("D" & d), Round(Abs(.Range("E" & d)), 2), .Range("D" & e)) .Range("E" & e) = .Range("E" & e) + .Range("E" & d) .Range("E" & d) = 0 Else Worksheets("Expenses").Range("F" & Lrow & ":H" & Lrow) = Array(.Range("D" & d), Round(Abs(.Range("E" & e)), 2), .Range("D" & e)) .Range("E" & d) = .Range("E" & e) + .Range("E" & d) .Range("E" & e) = 0 End If End If Next e Next d End With Application.ScreenUpdating = True End Sub
Event code, sheet 'Calculation'
Private Sub Worksheet_Calculate() Call SplitExp End Sub
If you want to learn more about array formulas join Advanced excel course.
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.