+ Reply to Thread
Results 1 to 6 of 6

Sum based on rank and total can't be more than 100

  1. #1
    Registered User
    Join Date
    04-11-2019
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    3

    Sum based on rank and total can't be more than 100

    Hi,
    • I have ranked countries based on interest rates. To be ranked they must be above a certain level.
      I also have by country weight. For an individual country this can be greater than 100%.

    example.JPG

    I want to create a new set of columns. Where countries are included on the condition that the total of the weights across countries is less than 100%. I want to look at countries in order of their rank. For example, in the first row, Switzerland and Germanly would be included as they are ranked 1 and 2 and their total weights is less than 100%. Japan would not be included, as including would bring the total weight of the 3 countries to more than 100%.
    Attached Files Attached Files
    Last edited by wazza1010; 04-11-2019 at 03:14 AM. Reason: Sample File Attached

  2. #2
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Sum based on rank and total can't be more than 100

    Pl upload a sample file showing the results manually calculated.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  3. #3
    Registered User
    Join Date
    04-11-2019
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    3

    Re: Sum based on rank and total can't be more than 100

    Thanks... I have uploaded a sample file.

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Sum based on rank and total can't be more than 100

    Do you want to include only First 3 ranks only not the 2nd+3rd+4th.

  5. #5
    Registered User
    Join Date
    04-11-2019
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    3
    Quote Originally Posted by kvsrinivasamurthy View Post
    Do you want to include only First 3 ranks only not the 2nd+3rd+4th.
    I want to include all ranks, so long as the total weight is less than 100.

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Sum based on rank and total can't be more than 100

    In the file you have taken only 2nd+3rd only, 4th is left out even though total of 2+3+4th is <100%.
    Put more data in all columns (Countries) and show how should be your result.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How do I quantify rank/percentile? Known: Min, Max, Median, Avg & Total Count
    By joeyjoejoeShabadooJr in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-30-2019, 05:35 PM
  2. Replies: 4
    Last Post: 07-13-2017, 05:45 PM
  3. Replies: 2
    Last Post: 04-27-2015, 08:50 AM
  4. Named Rank based on Total
    By Adam Schaefer in forum Excel General
    Replies: 6
    Last Post: 07-24-2014, 10:02 AM
  5. [SOLVED] How Can I take group of Total Scores to reveal name in rank 1st-7th
    By Tatonka33 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-10-2014, 06:56 PM
  6. Sum from multiple items in a list, based on rank, until a total is met
    By tim_71 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-19-2013, 09:17 PM
  7. Replies: 1
    Last Post: 08-15-2005, 05:05 PM

Tags for this Thread

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