+ Reply to Thread
Results 1 to 4 of 4

Allocate certain number dependent on average & percentage

  1. #1
    Registered User
    Join Date
    04-03-2011
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    21

    Question Allocate certain number dependent on average & percentage

    As per the attached.
    Column b are names:
    Column d are the total amount of listings the person has
    Column f is the calculated total amount of listings that person can advertise.

    At the top in Cell G2 is the total amount of allocations allowed which is to be divided equally over all the persons listings The more listings the more allocations. However, the formula I currently have doesn't equal the exact amount in Cell G2. It needs to. This is where my problem is.

    Currently my formula in column d is: =IF(ROUND($D6/$D$24*$G$2,0)=0,1,ROUND($D6/$D$24*$G$2,0))

    This is rounding close but it needs to be exactly what is in cell G2. (remembering that all these numbers can change - including G2)
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,318

    Re: Allocate certain number dependent on average & percentage

    This gets you closer, but I doubt you will ever get exact, unless by luck...
    =IF(D6=0,0,IF(ROUND($D6/$D$24*$G$2,0)=0,1,ROUND($D6/$D$24*$G$2,0)))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    04-03-2011
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Allocate certain number dependent on average & percentage

    awesome, many thanks. Real close now. Definitely as close as I will ever get. Thanks again.


  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,318

    Re: Allocate certain number dependent on average & percentage

    Happy to help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1