OT: Spreadsheet Fun!

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

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

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