by Nate Moore | Jul 22, 2013 | Arrays
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...
by Nate Moore | Jul 15, 2013 | Arrays
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...
by Nate Moore | Jul 8, 2013 | Arrays
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...
by Nate Moore | Jul 1, 2013 | Arrays
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...
by Nate Moore | Jun 24, 2013 | Arrays
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...
by Nate Moore | Jun 17, 2013 | Arrays
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...
