Select Page

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

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

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

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

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

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