Need a help for a formula

KWord

New member
Joined
May 13, 2012
Messages
3
Reaction score
0
Points
0
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).

ABC
1Month:APRIL
2Total Days:30
3Rent Fee:5000
4Per Day:167
5Students stayed:3
6
7Name:Days stayed:Need to Pay:
8Adam30=?
9Bob30=?
10John15=?
11Tom00

Thank you in advance.
 

CheshireCat

New member
Joined
Dec 30, 2011
Messages
121
Reaction score
0
Points
0
Location
Victoria, Canada
Excel Version(s)
Microsoft Excel 2013
In cell C8 try this:

Code:
=(B8/SUM($B$8:$B$11))*$B$3+B8*$B$4

and copy/autofill down.

Cheers,
 

KWord

New member
Joined
May 13, 2012
Messages
3
Reaction score
0
Points
0
CheshireCat,

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

ABC
1Month:APRIL
2Total Days:30
3Rent Fee:5000
4Per Day:167
5Students stayed:3
6
7Name:Days stayed:Need to Pay:
8Adam307010
9Bob307010
10John153505
11Tom00

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
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
And? ARe you saying thanks, or that it is wrong?
 

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
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
 
Last edited:

KWord

New member
Joined
May 13, 2012
Messages
3
Reaction score
0
Points
0
[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 :)
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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. :)
 
Top