Sort based on multiple variable conditions

1. 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. 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?

3. 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. 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. 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. 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. Re: Sort based on multiple variable conditions

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

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

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