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
[newsletter_signup_form id=1]
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
Extract dates from overlapping date ranges
The array formula in cell B3 extracts overlapping dates based on the date ranges in column D and E. Array […]
Merge Ranges is an add-in for Excel that lets you easily merge multiple ranges into one master sheet. The Master […]
Populate drop down list with unique distinct values sorted from A to Z
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]
How to use the NORM.INV function
The NORM.INV function calculates the inverse of the normal cumulative distribution for a given mean and standard deviation. Formula in […]
The picture above shows you conditional formatting formula that highlights matching records based on criteria in row 3 and 4. […]
Lookup multiple values across columns and return a single value
This article demonstrates how to get a value from a dataset based on multiple conditions across multiple columns. S.Babu asks: […]
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.
