formula to divide a monthly bill...

[Guest]

I'm sorry, but math is not my forte and I can't even think what I need to do
for this. I have a montly bill (every four weeks) that is split between a
certain number of people (right now it's 10), but if an individual is absent
for a period of time (weekly incements), their dues are deducted, but the
others have to pick up the slack so that the total bill is covered. Is there
anyone that can help me with a formula for this problem?

 

[Guest]

Can you give some more information as to how your spreadsheet is set up? For
example do you have each column set up for the weeks... and each row set up
for each person and what they owe?

 

[JE McGimpsey]

There are innumerable ways of splitting bills, depending on whether the
bills are variable or not.

If they're not, one way would be to divide the bill by the number of
"person-weeks", then assign each person that portion for each week they
were present.

For instance, if everyone is present for all four weeks, the split for
each person is

=(Total/(4*10)) * 4

If one person is absent for 1 week, then the split is:

9 people: =(Total/(4*9+3)) * 4
1 person =(Total/(4*9+3)) * 3

One way to set it up:

A B C D E F
1 NAME WK1 WK2 WK3 WK4 DUE
2 Alice X X X X
3 Bill X X X
....
11 James X X
12
13 Total Bill $100
14 Total Shares =COUNTIF(B2:E11,"X")

Then in F2 enter

=ROUND($B$13/$B$14 * COUNTA(B2:E2),2)

and copy it down to F11.

You may have to reconcile some pennies:

http://www.mcgimpsey.com/excel/pennyoff.html

 

[Guest]

It's very basic. The column A lists the names of the participants. Column B
lists what they owe for the month with the last cell in the column containing
the total of the bill for that month (it varies each month). Column C is for
myself to enter what they paid me. I create a new worksheet for each month.
I'm willing to make it more complex, but this is how I've been doing it the
last couple years. I know that Excel can figure this stuff out for me with
formulas, but I've been doing most of it by hand because I don't understand
the math that's needed (I'm a designer, I don't do math). The formula I
currently have in column B is just dividing the entry in the "total due" cell
by the number of people. If a person was out for one week then I add a
formula to subtract .25, for two weeks, .5, etc. But then I figure by hand
how much more the rest of the participants will need to pay to cover the
absence.
Thanks!

 

[JE McGimpsey]

I should have said, "depending on whether the bills are variable within
weeks or individuals, or not."

The bill can vary from month to month using the technique I described,
as long as the split won't vary based on which week or which person was
missed.

 

[Guest]

Awesome! I have a couple questions:

What does the "3" in (4*9+3) represent in

9 people: =(Total/(4*9+3)) * 4
1 person =(Total/(4*9+3)) * 3

Using this spreadsheet, I'm assuming I will have to change the formula in
each cell each month depending on how many people are absent and for how
long...would I not have to do that for the second spreadsheet suggestion?

Thanks!!

 

[Guest]

Jumping in for JE here -

The 4*9 represents 4 weeks * 9 people, plus 3 weeks for the person who was
out for a week. If two of your mates were each out a week, the formula for
each of the 8 with perfect attendance would be

=(Total/(4*8+2*3)) * 4

while the two who were out would pay

=(Total/(4*8+2*3)) * 3

JE's other solution works with no modifications (so long as you accurately
record blanks for those who are out & Xs for those who are in)