Home |
Search |
Today's Posts |
#1
|
|||
|
|||
formula to divide a monthly bill...
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? |
#2
|
|||
|
|||
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? -- Regards, DaveB "Kim" wrote: 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? |
#3
|
|||
|
|||
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 In article , Kim wrote: 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? |
#4
|
|||
|
|||
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! "DaveB" wrote: 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? -- Regards, DaveB "Kim" wrote: 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? |
#5
|
|||
|
|||
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. In article , JE McGimpsey wrote: There are innumerable ways of splitting bills, depending on whether the bills are variable or not. |
#6
|
|||
|
|||
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!! "JE McGimpsey" wrote: 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 In article , Kim wrote: 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? |
#7
|
|||
|
|||
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) "Kim" wrote: 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!! "JE McGimpsey" wrote: 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 In article , Kim wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
dragging a formula | Excel Discussion (Misc queries) | |||
help with spaces in functions (Biff) | Excel Worksheet Functions | |||
Named SUM Formula with relative refernce(s) | Excel Worksheet Functions | |||
How do I divide, what would the formula look like? I forgot. Than. | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |