Split expenses calculator
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, […]
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 Download […]
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
Paste image link to your comment.