OT: Spreadsheet Fun!

[AgentZero]

Hiya folkeses! Got another wee problem I could use a hand with. I live in a shared house with 3 others so when the bills come due, we always have great fun. I'm trying to convert our system into a spreadsheet to cut down on errors, since when I went to pay the bills today, everyone had chipped in what was calculated they owed, but I ended up 40eur short and had to leave, get the extra money, then re-queue for another 15mins to get the bills paid. All of which did not leave me a particularily happy camper.

So, the way our system works is we take the value of the electrical, gas, and cable and divide by four. The phone bill's a bit different because we split the line rental by 4, then everyone adds on the value of the calls they made. So far so good? Good, coz here comes the fun part.

Whenever anyone buys something for the house (washing up liquid, light bulbs, cleaning supplies, etc.) they stick their name on the reciept, then as the final step of calculating what everyone owes, we add the total value of all the reciepts, then divide by 4 to figure out what, all things being equal, each of us should have paid, then add them up again according to who actually paid what. Then, the difference between what you did pay and should have paid is either added or subtracted from what you owe for the bills. Still with me?

Now, what I want to do is make a spreadsheet or something similar where we can just plug in the values and it'll tell us what each of us owe.

I've done a mockup of what I'd like it to look like, see the attachment for that. Now, does anyone know how to get the spreadsheet to do all the calculations for me so I can just plug in the values into collumn 'B' and it'll fill in the rest for me?

[AgentZero]

OK, I've figured out everything up to and including Sub-Total (wasn't too hard really), but the reciepts bit is giving me a bit of trouble.... Any suggestions of a formula for that?

[Fyron]

By the way, having all of the Phonecalls and Receipts in single rows makes it easer to drag the other formulas to the right to prevent having to write so much. There is a little dot in the lower left corner of a selected box.

AgentZero said:
OK, I've figured out everything up to and including Sub-Total (wasn't too hard really), but the reciepts bit is giving me a bit of trouble.... Any suggestions of a formula for that?

If you make them and the phonecalls be in singular rows, you get:

Bill Total + [Name] Phonecalls + Average Receipts - [Name] Receipts

[AgentZero]

OK, that bit's done now too, so now for the really fun bit. Every now and then someone pops off for a holiday or something and as a result doesn't have to pay a certain bill, since they weren't there.
Is there any way of putting in exemptions so it won't apply a certain bill to a certain person if we tell it to?

Hey neat! Just noticed I got a little cake by my name. Happy Birthday to me!

Edit: Thanks Fyron! Couldn't be bothered re-aranging everything (work on my birthday? A pox on you!) but your formuals still worked nicely even with the extra clicking.

[Fyron]

You can overwrite the /4 formula with a 0 for that month.

[AgentZero]

Well, that was Plan B. Since I won't always be the one using the sheet I was hoping to protect all the formula cells to prevent less computer literate people from fudging the whole thing up.
Any other options?

[Fyron]

Hide a backup of the SS somewhere!

[Suicide Junkie]

Imperator Fyron said:
You can overwrite the /4 formula with a 0 for that month.

That would leave you with only 3/4ths of your bills paid.

Why not make a row in which you fill in the number of days (or weeks) that month the person was present.

The the divide by 4 becomes:
divide by sum(Presence row), times presence cell for that person.

If the person was there for 2 weeks, while the other three were there for 4 weeks, then the 2-weeker pays 2/14ths of the bill.

If you don't want to measure weeks, and only months, that works too. As long as you ALL don't leave, resulting in a division by zero-people-to-pay-up

[narf poit chez BOOM]

Happy birthday.

Now, what are you doing doing math on your birthday when you don't have to?

[Fyron]

Happy birthday!

You might want to consider getting a copy of OO beta 2.0. Its much shinir. Or at least the latest stable release, 1.1.5.

Anyways... To make a formula in a column, enter something like this in cell C2:

=B2/4

Bill total would get:

=SUM(C2:C5)

You might mant to make the Phonecalls data be a single row for better readability.