I want to split our joint credit card bill but have some items I pay and some my partner pays. I have 2 columns headed A and B with a sub total at the bottom for our respective personal spends. Now let's say the total bill is £300 entered in a single cell, but on that bill my personal spend is £20 and my partners personal spend is £40. So the total to split would be £240, or £120 each plus our personal spends. What would be the formula under each of our respective columns to show what we each owe? TIA.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Split the Bill
- Thread starter AndyB63UK
- Start date
MARK858
MrExcel MVP
- Joined
- Nov 12, 2010
- Messages
- 16,184
- Office Version
- 365
- Platform
- Windows
- Mobile
Can you show your exact layout using the boards XL2BB add-in please (the XL2BB icon in the reply window is a link to the instructions and download link or click the red underlined XL2BB in my signature block which is also a link).
Upvote
0
Toadstool
Well-known Member
- Joined
- Mar 5, 2018
- Messages
- 2,549
- Office Version
- 2016
- Platform
- Windows
Hi AndyB63UK,
Something like this?
Something like this?
| Book1 | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Me | Partner | Me | Partner | Total | ||||
| 2 | £ 1.00 | £ 18.00 | Personal | £ 20.00 | £ 40.00 | £ 300.00 | |||
| 3 | £ 12.00 | £ 14.00 | Split | £ 120.00 | £ 120.00 | ||||
| 4 | £ 4.00 | £ 8.00 | To Pay | £ 140.00 | £ 160.00 | ||||
| 5 | £ 3.00 | ||||||||
| 6 | |||||||||
Sheet1 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E2:F2 | E2 | =SUM(A:A) |
| E3:F3 | E3 | =($G$2-SUM($E$2:$F$2))/2 |
| E4:F4 | E4 | =SUM(E2:E3) |
Upvote
0
MARK858
MrExcel MVP
- Joined
- Nov 12, 2010
- Messages
- 16,184
- Office Version
- 365
- Platform
- Windows
- Mobile
With that layout you can just amend Toadstool's formula.
| Book2 | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | Visa CC Bill | Me | Partner | Total | ||||
| 2 | Total | £120.00 | £120.00 | £300.00 | ||||
| 3 | Andy | Chris | £140.00 | £160.00 | ||||
| 4 | £1.00 | £18.00 | ||||||
| 5 | £12.00 | £14.00 | ||||||
| 6 | £4.00 | £8.00 | ||||||
| 7 | £3.00 | |||||||
| 8 | ||||||||
| 9 | ||||||||
| 10 | £20.00 | £40.00 | ||||||
Sheet1 | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D2:E2 | D2 | =($F$2-SUM($A$10:$B$10))/2 |
| D3:E3 | D3 | =SUM(D$2,A$10) |
| A10:B10 | A10 | =SUM(A4:A9) |
Upvote
0
That did the trick, many Thanks
| Household Bills.xlsx | |||||
|---|---|---|---|---|---|
| A | B | C | |||
| 1 | Visa CC Bill | ||||
| 2 | Total | 300 | |||
| 3 | Andy | Chris | |||
| 4 | £1.00 | £18.00 | |||
| 5 | £12.00 | £14.00 | |||
| 6 | £4.00 | £8.00 | |||
| 7 | £3.00 | ||||
| 8 | |||||
| 9 | |||||
| 10 | Personal | £20.00 | £40.00 | ||
| 11 | Split | £120.00 | £120.00 | ||
| 12 | To Pay | £140.00 | £160.00 | ||
Visa Bill | |||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B10:C10 | B10 | =SUM(B4:B9) |
| B11:C11 | B11 | =($C$2-SUM($B$10:$C$10))/2 |
| B12:C12 | B12 | =SUM(B10:B11) |
Upvote
0
Similar threads
- Solved
- Solved
