Excel Video 183 uses INDEX and MATCH to look up which payer has the highest and lowest reimbursement for a given CPT code. You’ll see in Excel Video 183 that I have a variety of statistical information about how different payers reimburse for a given CPT code. By using INDEX and MATCH, I can look up which payer pays the maximum value and the minimum value in my range of data.
Don’t get too hung up when I use MEDIAN. Briefly, to calculate a median you put all of your values in ascending order and then choose the middle value. If you have an even number of values, you still put the values in ascending order but now there are two values in the middle. For example, in a group of 6 values, the third and fourth values are in the middle when you sort the values in ascending order. To calculate the median of an even number of values, you simply take the average of the two middle values, in our example, the average of the third and fourth values. If you have an odd number of values and calculate the median, INDEX and MATCH will be able to look up that value. If you have an even number of values and calculate the median, since Excel has to take an average of the two middle values, INDEX and MATCH likely won’t find an exact match and you’ll get an error message.
There’s one other thing to watch for in Excel Video 183. The COUNT function only counts numbers. If you want to count non-numeric, non-empty cells, try using COUNTA.
That’s what I wanted to show you for INDEX and MATCH. Stay tuned. Next time we’ll start looking at some functions that allow you to do sum, count, and average calculations with conditions.
