Results 1 to 7 of 7

Thread: Need a help for a formula

  1. #1

    Need a help for a formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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).

    A B C
    1 Month: APRIL
    2 Total Days: 30
    3 Rent Fee: 5000
    4 Per Day: 167
    5 Students stayed: 3
    6
    7 Name: Days stayed: Need to Pay:
    8 Adam 30 =?
    9 Bob 30 =?
    10 John 15 =?
    11 Tom 0 0

    Thank you in advance.

  2. #2
    Conjurer CheshireCat's Avatar
    Join Date
    Dec 2011
    Location
    Victoria, Canada
    Posts
    118
    Articles
    0
    In cell C8 try this:

    Code:
    =(B8/SUM($B$8:$B$11))*$B$3+B8*$B$4
    and copy/autofill down.

    Cheers,

  3. #3
    CheshireCat,

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

    A B C
    1 Month: APRIL
    2 Total Days: 30
    3 Rent Fee: 5000
    4 Per Day: 167
    5 Students stayed: 3
    6
    7 Name: Days stayed: Need to Pay:
    8 Adam 30 7010
    9 Bob 30 7010
    10 John 15 3505
    11 Tom 0 0

    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

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

  5. #5
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    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 by Simi; 2012-05-14 at 03:58 PM. Reason: added extra notes for the formula chesirecat provided

  6. #6

    [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

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,209
    Articles
    57
    Blog Entries
    14
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •