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]

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.

[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!

[inigma]

What version of Excel?

As the spreadsheet owner, you can lock cells, columns, and rows with a password. Go to "format cell" then make go to the "protection" tab. Then go to Tools/Protection and protect the doc and it will protect cells that are marked locked. You can even protect the whole doc (the default) and then select a group of cells and unprotect, securing the whole document, but still allowing others to update the cells you specify.

I knew that official MS certification for MS Excel course that I paid $70 USD for would come in handy someday.

[AgentZero]

Actually it's Open Office program which is a lot like MS Office only better and free. I'm really looking for a way to exempt certain cells if need be so I can protect the cells I don't want fiddled with but still have them not apply in certain cases. Dunno how possible that is though....