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

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

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

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

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

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

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

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

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

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

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

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

Excel Video 358 has another way arrays might make your life easier. I have five clinical measures that I’ve summed and then rounded the result. If I want to round the measures first and then sum the result, you guessed it, arrays can help. We’ve done several examples...

As promised, there’s an easier array example in Excel Video 357 today. In today’s example we’ll assume that someone’s been counting something such as no shows, patients who arrived more than 15 minutes late for their appointment, or patients needing an xray. To count,...

Excel Video 356 offers one more complicated formula, but once that formula works, it’s very powerful. We’re going to do an OR criteria in an array today. If you missed Excel Video 355, please take a minute to watch it first. Today’s video will make a lot more sense....

I hope you don’t have to spend a ton of time rewinding Excel Video 355. The array formula to sum multiple criteria at once is complicated. The SUMIFS formula in Excel Video 185 is a much easier approach to summing a range of data that meets multiple criteria. The...