## Split expenses calculator

*Article last updated on July 31, 2017*

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

Sub SplitExp() Dim r As Single, d As Single, e As Single, Lrow As Single Application.ScreenUpdating = False With Worksheets("Calculation") r = .Range("H1") .Columns("D:E").Clear Worksheets("Expenses").Range("F2:H100").Clear .Range("D" & 1 & ":E" & r) = .Range("A" & 2 & ":B" & r + 1).Value 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.

### Download excel *.xlsx file

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 [Array formula]

This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]

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 […]

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, […]

Sort cell values into categories

Here are the prerequisites. Column A contains dates, column B are categories and column C are values. The goal with […]

Categorize values into multiple columns (excel formulas)

In a previous post I created unqiue distinct column headers (A20:C20) and then categorized adjacent cell values into each column […]

Categorize data entry values [VBA]

In a previous post I described how to simplifiy data entry. Now it is time to put values in separate […]

Categorize values into multiple columns [VBA]

I am fairly new to vba and I am amazed of how much you can automate in excel. In this […]

Sam asks: S/N RailCorp Ref Number Date In 77203 HRC mod program 10377 24/05/2011 77204 HRC mod program 10285 20/04/2011 […]

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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form