Weighted Values

[Guest]

I run a water fund for my office, and I'm trying to fairly account for people
who leave the office for business and don't use the water for anywhere from a
week to a month.

Currently, I'm entering the invoice total in one cell, and in another
dividing it by the total participants minus a countif function (I mark an "X"
by their name for the month if they are out for a significant amount of time).

This is a little clunky, though, and I'd like to account for people who are
out 1-3 weeks by using something like a .25-.75 system, so nobody pays too
much for water they don't use or pay for someone else.

Any suggestions?

Thank you

 

[Guest]

Say we list each person in column A and the number of days in a month they
are in-office in column B.

The sum of column B is the number of people-days. Divide the monthly water
cost by this and then mulitply by column B to get column C. For example, if
the monthly water cost is $111.00:


Larry 20 39.64
Moe 20 39.64
Curley 10 19.82
Rocky 5 9.91
Bullwinkle 1 1.98

TOTAL 56

Where C1:
=Round(B1*110/B$7,2) and then copy down

 

[Guest]

With this range in A16
Name WeeksOff BillAmt $100.00
Allen 1 (blank) (blank)
Bev 1 (blank) (blank)
Carl 0 (blank) (blank)
Donna 0 (blank) (blank)
Ed 0 (blank) (blank)

Where:
Col_A contains Names
Col_B contains the number of weeks to deduct
Col_C will contain the person's bill
D1 contains the amount of the water bill

This formula calculates each persons bill for the month:
C2: =ROUND($D$1*(4-B2)/(SUMPRODUCT((4-$B$2:$B$6))),2)
Copy that formula down

Does that help?
***********
Regards,
Ron

XL2002, WinXP