Arrays Part 2 Playlist
Excel Video 359 Using SMALL to Find the Third Next Available Appointment
Excel Video 359 has a way you might start using arrays in your practice. If you can download your open appointment slots into Excel, the SMALL function can help you find the next available appointment, the third next available appointment, and more. SMALL is a function that uses an array as an input. We’ll combine SMALL as part of a larger array formula in the next Excel Video to look for specific types of appointments. I look forward to seeing you then.
Excel Video 360 Using Arrays with SMALL, Part 1
Excel Video 360 uses arrays with the SMALL function to find the next available appointments. The first example uses an IF statement as part of the array component of the SMALL function to find the third next available new patient appointment. The IF statement limits the SMALL function to only look for open appointment slots for new patients. We’ll expand on this logic in the next Excel Video.
The second example uses the existing array in the first part of the SMALL function (so we’re looking for all new appointments, not just new patient appointments) and uses an array for k, the second part of the SMALL formula. Remember from the last Excel Video that k tells Excel if you want the first smallest value, the second smallest value, etc. By putting an array in for k, enclosed in {braces} and separated by semicolons, we can get the first, second, and third next available appointment in one formula instead of entering the formula three times.
There’s more we can do with arrays in the SMALL function. We’ll keep going in Excel Video 361. I look forward to seeing you then.
Excel Video 361 Using Arrays with SMALL, Part 2
We’ll write a little more complicated formula in Excel Video 361. It might help to review Excel Videos 355 and 356 since today we’ll look for the third next available appointment for a new patient in the South location. To find an appointment that’s both new and south, we need to use multiplication in the IF statement. We’ll work through that formula example today and you can use the same logic to find a new patient in the south location on a certain day of the week.
Speaking of days of the week, there’s a preview at the end of this Excel Video for where we’ll go next. You’ll see me try to find an appointment (from a very small list of fake appointments) meeting a bunch of criteria that are easier to manage because the criteria are outside the big, long formula. Stay tuned next time and I’ll show you how I built that formula. Thanks for watching.
Excel Video 362 Using Arrays with SMALL, Part 3
Excel Video 362 takes our SMALL functionality to the next level by adding two more functions to the mix. If VLOOKUP is new to you, it might help to watch the VLOOKUP playlist starting with Excel Video 61. If you aren’t familiar with WEEKDAY, watch Excel Video 165. We’ll use WEEKDAY to calculate the date of the week in column B and VLOOKUP to look up a matching number for the day of the week we enter in cell F22. If those numbers match and the appointment type and location match, the array picks the next available appointment.
We’ve made this array fairly complex, but the point is that arrays can quickly loop through a range of cells, apply a series of formulas, and return a quick answer. You might have to troubleshoot the array formula the first time, but once it works, you’ve got a powerful way to analyze a lot of data quickly.
Stay tuned. Instead of looking for the first, second, or third next available future appointment, next time we’ll look to the past to see the last referral from a set of referring physicians. I look forward to seeing you then.
Excel Video 363 Using LARGE to Find the Last Referral
Instead of looking for the next future appointment, Excel Video 363 looks for the most recent past appointment from a referring physician. We’ll switch from SMALL to LARGE today to look for the largest past appointment, which turns out to be the most recent appointment from a referring physician. The LARGE formula works just like SMALL does, so if you missed the last couple of Excel Videos, it’s worth going back and watching from Excel Video 359 forward. Today’s Excel Video will make a lot more sense if you have that background.
I hope you can see how powerful these array formulas can be. Stay tuned. We’ll add a little more complexity by adding OFFSET and MATCH next time.
Excel Video 364 Using LARGE to Find the Last Referral Part 2
Excel Video 364 uses LARGE to find the last referral and OFFSET and MATCH to get the details about that referral. If you’ve watched the last several Excel Videos, using LARGE to calculate the last referral date will be pretty straight-forward. This time we’ll take the provider and last referral date to look up the referring physician, location, and specialty for the last referral.
If OFFSET and MATCH are new functions for you, you might find the series starting with Excel Video 87 helpful for OFFSET and Excel Video 179 helpful for MATCH. Stay tuned. I have a different array trick coming up next.
Excel Video 365 Using Arrays to Sum Ranges with Errors
Excel Video 365 has a clever way to sum a range of data even if the data has errors in it. You may have noticed that if your data has an error message in it, Excel formulas like SUM show the error instead of the total. My first choice is always to fix the error, but if you can’t fix the error for whatever reason, you can use an array formula to ignore the error messages and get a sum.
Watch for how the ISERROR function works. That’s the key to making our array formula work. ISERROR is true if there’s an error and false if there’s not an error. Let me be clear. By error I mean things like dividing by zero or looking up a value that isn’t there. Excel can’t give you an error message if you type the wrong number or you reference the wrong cell. By using SUM and IF with ISERROR, we can change all of the error values to an empty string “” and just sum the remaining values. It’s a clever trick. We’ll take it a step farther in the next Excel Video.
Excel Video 366 Using Arrays to Average and to Count Errors
Excel Video 366 has more tricks with IF, ISERROR, and arrays. Today we’ll use ISERROR to look for errors and then use the IF statement to decide what to do with the errors. Note that if we replace errors with two double quotes “”, Excel will not count the error cell in the average calculation since AVERAGE ignores blank cells. On the other hand, if we replace the error with a 0, AVERAGE will include the 0 in calculating the average number of surgeries. It’s just a matter of deciding how you want to handle potential errors and knowing what Excel functions do with blank cells compared to zeros.
Watch for a clever way to count the number of errors in my range. You could use this in a variety of places in a spreadsheet to make sure that you don’t have any errors before you save, print, or give the spreadsheet to a physician or an administrator. Now that we’re familiar with arrays, counting errors is a fairly easy function to put together.
Excel Video 367 Using Arrays to Exclude Values
In the past couple of Excel Videos we’ve found and replaced errors in our data. In Excel Video 367, we’ll find and exclude zeros. Instead of using ISERROR like we have in the past couple of Excel Videos, today we’ll use , which is Excel-speak for not equal to. We’ll find all of the data that is not equal to zero and then take an average. You could easily use similar logic to find all locations that weren’t in the hospital or exclude all procedure codes that did not begin with 99.
Errors and zeros are nice to find. In the next Excel Video, we’ll look at differences in contractual allowances. I look forward to seeing you then.
Excel Video 368 Using Arrays with ABS
No, Excel Video 368 isn’t a fitness videos and arrays won’t help you with your abs. ABS is an Excel function that takes the absolute value of a cell. We’ll take the absolute value of a range of cells by combining ABS with an array today.
Also watch for how I write a SUMIF formula that sums all negative values. It’s not an array, but you might find it helpful. The formula is =SUMIF(F4:F18,”
Excel Video 369 Using Arrays with TRUNC and Using Arrays to Sort
Excel Video 369 introduces the TRUNC function which drops everything after the decimal. TRUNC may not be quite as elegant as ROUND is, but it may come in handy for you. Watch how I combine SUM, ABS, and TRUNC in one array to calculate the sum of the absolute value of the truncated numbers.
We’ve played with SMALL and arrays in Excel Videos 360 through 362, but here’s another example you might find useful. Watch how I can use SMALL to sort and find the top three appeal issues that I need to work on first. SMALL and LARGE are great ways to sort data on the fly.
I have a new dataset to discuss more array tricks next time. I look forward to seeing you then.
Excel Video 370 Using Arrays to see if Data is Sorted
Excel Video 370 has an array formula that tells you if your list isn’t sorted. The trick is to know that Excel treats B as being greater than A, so our array formula will catch times when A comes after B in a sorted list. Remember the AND function is true if all of the arguments in the function are true. Our formula will be false if the data isn’t sorted in alphabetical order.
Adding the IF function to the AND formula allows us to display a warning message when the data isn’t sorted and to leave the cell blank when the data is sorted. It’s a clever way to track lists that you expected to be sorted. Thanks to Chandoo.org for the idea for this video and the next. The next array trick is to tell if a list is unique. We’ll work through that example next time.
Excel Video 371 Using Arrays to See if a List is Unique
I have one more array trick in Excel Video 371 before we move on to a new topic. Today we’ll use a COUNTIF function inside a MAX function inside an IF function to determine if a list is unique. It’s really not quite as bad as that sounds. We’ll set up a COUNTIF function first to determine the number of times a doctor’s name appears in a list. Then we’ll use the MAX function to determine the maximum number of times a doctor’s name is on the list. Finally, the IF function will print a message in the cell determining whether the list is unique. Once the formula is written, all you need to do is to change the size of the range (or name the range) to manage different lists.
I’ve been putting off Excel Videos about Excel 2013 until we made it through some array formulas. Now that we’re through arrays, we’ll start with Excel 2013 next time. I look forward to seeing you then.
