+ Reply to Thread
Results 1 to 6 of 6

incrementing high mod values in a list

  1. #1
    Registered User
    Join Date
    11-28-2016
    Location
    Boise, ID
    MS-Off Ver
    Several
    Posts
    4

    incrementing high mod values in a list

    Hello,
    I am making a spreadsheet of apportionment schemes. I'm sure I could find it all done someplace, but I would like to see how to approach this problem. I am stuck on Hamilton's method. If you don't know what that is, it is unimportant - please read on.
    I have a column of 50 floating point numbers and an integer between 0 and 50, which I will call N. For purposes of this post, I will call the portion of the floating point number to the right of the decimal point the fraction, so for an entry of 13.876 the fraction is .876
    I want to create a new column of 50 integers, where the N entries in the original list with the highest fractions are replaced by their ceilings, and replace the rest by their floors. In other words, the number N tells us how many members of the list get rounded up and then the rest get rounded down. The new list must have the entries in the same order as the ones in the original list.
    Conveniently, I have a column of 50 names in alphabetical order corresponding to the 50 original values. So presumably I could take mods of the list to get a list of the fractions, then sort all three lists using the fraction list as the key, take the ceilings and floors now that they are grouped with the ceilings on top and floors on the bottom, and then use the alphabetical list to un-sort back to the original order. That would be easy to do interactively, but I want it all done within the sheet, as a macro, and not only have I never done anything like that before, but it seems quite cumbersome. Can anyone suggest another way to handle this?
    Thanks very much.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,925

    Re: incrementing high mod values in a list

    Please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    11-28-2016
    Location
    Boise, ID
    MS-Off Ver
    Several
    Posts
    4

    Re: incrementing high mod values in a list

    OK, here is a smaller example, only 10 entries and the final results mocked up. Column C has the numbers of interest, and in this example, N=3. Column D has the results. You can see that the values in D1, D9, and D10 have been rounded up from their values in column C, the rest have been rounded down.
    Attached Files Attached Files

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,925

    Re: incrementing high mod values in a list

    Have you looked at the RANK() function? I think it will work for you.

  5. #5
    Registered User
    Join Date
    11-28-2016
    Location
    Boise, ID
    MS-Off Ver
    Several
    Posts
    4

    Re: incrementing high mod values in a list

    I'll gie it a try. It does look good.

  6. #6
    Registered User
    Join Date
    11-28-2016
    Location
    Boise, ID
    MS-Off Ver
    Several
    Posts
    4

    Re: incrementing high mod values in a list

    Yes, it does work. Thank you very much.

+ 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. Sort list into another list filtered low>high and only ones <90
    By InvalidTxtString in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-08-2016, 05:44 PM
  2. High & Low Values
    By Sasha 99 in forum Excel General
    Replies: 3
    Last Post: 07-22-2016, 01:04 PM
  3. Creating a list with incrementing decimals
    By probuddha in forum Excel General
    Replies: 7
    Last Post: 04-07-2015, 06:49 AM
  4. Replies: 12
    Last Post: 11-07-2012, 01:37 PM
  5. Excel 2007 : High School Scheduling List
    By Marylea in forum Excel General
    Replies: 6
    Last Post: 04-21-2012, 04:34 PM
  6. Incrementing values
    By ady_mech in forum Excel General
    Replies: 2
    Last Post: 12-20-2007, 09:35 AM
  7. High score list?
    By nc-nc in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 01-17-2005, 07:06 AM

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