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.
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Text to columns: Split words in a cell
This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]
Split values equally into groups
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
Split words in a cell range into a cell each [UDF]
This post describes how to split words in a cell range into a cell each using a custom function. I […]
Rearrange values based on category [VBA]
In this post I am going to rearrange values from a list into unique columns. Before: After: The code Get […]
How to group items by quarter using formulas
This article demonstrates two formulas, the first formula counts items by quarter and the second formula extracts the corresponding items […]
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.
