+ Reply to Thread
Results 1 to 9 of 9

Sort based on multiple variable conditions

  1. #1
    Registered User
    Join Date
    03-14-2012
    Location
    Colorado
    MS-Off Ver
    Office 2010 & 2007
    Posts
    5

    Sort based on multiple variable conditions

    I'm creating a spreadsheet that will take about 500 different medical plans and sort them based on how close they are to a clients current plan. I'm looking at about 6 columns with information in them that I would sort on. Two examples of the data colums would be deductible and doctor visit copay.

    Example: companies current plan has a $1,500 deductible and a $20 doc visit copay. I put those two values in and the 500 plans are sorted based on the deductible and then the doc copay. Here's the kicker I need help with. The company may say we want the deductible to be $1,500 or less or they might say it can be a little more. But in the end I need to sort the plans that are the closest match (above and below) to their variables.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Sort based on multiple variable conditions

    Can you add a helper column that calculates the difference, then sort the whole thing based on that column's results?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    03-14-2012
    Location
    Colorado
    MS-Off Ver
    Office 2010 & 2007
    Posts
    5

    Re: Sort based on multiple variable conditions

    Can you elaborate on what you mean by a helper column? I do have control over the colums since this is being developed from the ground up.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Sort based on multiple variable conditions

    I mean you can possibly add a formula in another column which calculates the matches (above and below) and then you can sort on that column so that the closest (or smallest differences) are at top or bottom based on what you want.

  5. #5
    Registered User
    Join Date
    03-14-2012
    Location
    Colorado
    MS-Off Ver
    Office 2010 & 2007
    Posts
    5

    Re: Sort based on multiple variable conditions

    Yes I can add a column for that. If the plan had a $1,500 deductible then $1,750 and $1,250 would be closer matches than $1,000 and $3,000. How would you structure the formula so the $1,750 and $1,250 show first? Would you use a percentage variable or something else?

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Sort based on multiple variable conditions

    I would do something like:

    =ABS(E2-1500)

    where E2 contains the first amount (say $1,750) and you copy down the formula.

    If 1500 deductible is contained in an input cell somewhere, then you can replace that number in the formula with that cell and absolute reference it, like:

    =ABS(E2-$X$1)

    now you can sort by this column.

  7. #7
    Registered User
    Join Date
    03-14-2012
    Location
    Colorado
    MS-Off Ver
    Office 2010 & 2007
    Posts
    5

    Re: Sort based on multiple variable conditions

    Thanks, let me give it a run and see how it works out.

  8. #8
    Registered User
    Join Date
    03-14-2012
    Location
    Colorado
    MS-Off Ver
    Office 2010 & 2007
    Posts
    5

    Re: Sort based on multiple variable conditions

    I decided to add helper columns that figured the percentage match to base numbers. This gives me positive and negative percentages so now I want to sort the percentages so the ones closest to zero are at the top of the list any ideas?

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Sort based on multiple variable conditions

    Wrap the formula you created in an ABS() function so that you get only positive values . then you can sort.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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