Need a help for a formula

Hi,

Can anyone please help to find a formula for this case?

This is a table of shared monthly fee for a rented house. I can't make a proper formula (from C8 to C11 cells) which will calculate the rent fee for each student according to stayed days and share among them (B5) respectively.

Pre-conditions:
-Monthly rent fee is always fixed;
-Daily fee varies according to current month;
-A student who didn't stay a single day should be excluded from the list (in this example there are 4 student, but Tom is excluded in B5).


Thank you in advance.

In cell C8 try this:


and copy/autofill down.

Cheers,

CheshireCat,

First of all, thanks for help. The result that I got is here:


Where:

C8=(B8/SUM($B$8:$B$11))*$B$3+B8*$B$4
C9=(B9/SUM($B$8:$B$11))*$B$3+B9*$B$4
C10=(B10/SUM($B$8:$B$11))*$B$3+B10*$B$4
C11=(B11/SUM($B$8:$B$11))*$B$3+B11*$B$4

And? ARe you saying thanks, or that it is wrong?

I believe he is saying that it is wrong considering 7010 is more than the required 5000 from B3.

Try this.

B4 =B3/SUM(B8:B11)
B5 =COUNTIF(B8:B11,">0")
C8 =$B$4*B8

Some notes, the range in cell c4 and c5 needs to be updated as you add more people to the list.
the formula in D8 can be copied down.

Simi

Or to simplify what ChesireCat was doing.

You can simply use this in C8 and copy down. This gives each person a percentage of the total days stayed, then just divides the total accordingly. If you still want to see the values for $per day, and # of students stayed; you still need the formulas for B4 and B5 I posted above.
C8 =(B8/SUM($B$8:$B$11))*$B$3

[SOLVED]: Need a help for a formula

Many thanks for everyone for clues and formulas. Everything solved exactly as I needed.

I'd give stars, but unfortunately this feature was not available

Hi KWord,

Thanks for posting back that this has solved your issue. I've never enabled the stars feature only because it is so rarely used that it looks odd on the forums. I may add a "thanks" feature in future. but haven't yet investigated them to any degree. I do appreciate the sentiment though.