@msexcel regularly remind the Excel community of some of the useful functions and formula that help with finance, business analysis, data analysis and all the other jobs that Excel gets used for. Here we’ve a few illustrations inspired by @msexcel.

One or the other … not both

“XOR” a logic function that returns “true” when its two arguments differ. So if you want to know if two logical values are not the same use “XOR”. “XOR” along with the other logical functions have a lot of uses, and is often the basis for building the condition part of “IF” statements. Here’s an illustration of “XOR” in action:

xor examples

By the way, you’ll see that 0 and 1 in Excel can also stand for FALSE and TRUE.

Catching errors

“IFERROR” is a more sophisticated, it used to trap errors in a computations. Normally we don’t expect or want an error in a computation but sometimes it happens. An error might arise if a user input is not what was expected or a data set has an oddity that has not been spotted.

So, imagine the computation you want to do is F and it works just as you want, but on occasions its inputs might result in an error. What you do is “wrap” it in “IFERROR”. So you use the formula “=IFERROR(F,G)” – here F is the computation you wanted to do and G is what you’d like to compute if F gives an error. Often for users, G might be a string like “Whoops, something went wrong”. Though something more informative about avoiding the error might help. On other occasions G might simply be a reasonable value (given that the F value is not correct).

In this illustration of “IFERROR” we have F as a divide operation and G as a divide operation also. Watch the illustration. You can see that when the first argument F is an error, the value from the second argument is used. If there is no error in the first argument the second argument is ignored. Of course if G is an error it only shows when F is also an error.

iferror examples


Share This