With Excel 2016 and Office 365 Pro plus, a less confusing and powerful function was introduced – IFS function. It eliminates the confusion generated by multiple nested IFs for complex calculations. In any case, if you are using multiple nested IF statements, check whether VLOOKUP can solve the issue or use Pivot Table grouping.
Nested IFs look like this:
The same formula using IFS function is much simpler to type and understand :
IFS function has just ONE set of brackets, irrespective of the number of conditions used. It also offers the last argument for default return value – in case all other conditions did not produce any output. Try using it.
The function will return an error if used in versions before Excel 2016. Be careful.
When you type something in a cell after an = sign in Excel, a list of functions is shown. Functions have an icon next to them. For some functions, the icon shows a Yellow triangle with an exclamation mark. That is the Excel Function Warning I am talking about.
No warning Warning
What exactly is the Excel Function Warning?
It basically means: “Do not use this function. We have a new and more accurate function available now”. If you use only newer versions of Excel, you should use the newer functions.
Here is an example. The older STDEV function now has two separate functions STDEV.S and STDEV.P – S means sample and P means population. The mathematical formula used behind the scenes by these functions is different. As mathematics advances further, the logic used also undergoes improvement. The newer functions utilize the latest algorithms to increase accuracy of your results.
- Notice if any of the your formulas have one or more Excel Function Warning icon.
- Go to the help file of that function.
- The newer, better or more appropriate function will be listed there.
- Learn the new function(s). Understand what has changed and why.
- Change these across your files to ensure more accurate results.
- Use the new function when you create new workbooks.
So far, I have not written an article about the humble IF function. But in the last few months I came across many customers who asked me to explain how the IF function works. Surprised? The reason is that many people use the Excel IF function, but they do not understand its nuances.
Usually someone has created a file and is handed over from person to person. Worse still, those who know how to use the IF function misuse it! So here it is a simple, short and practical description of how to use the IF function.
Continue reading Excel IF function – Dos and Don’ts
This is a picture blog. Self-explanatory! Learn how to Sum visible cells only using the Aggregate function.
Works only in vertical direction (not horizontal). It is best to use it with Excel Tables as shown above. That way, adding more data will automatically update the formula results.
Works from Office 2010 onwards.
Like column names, table names can also be used in formulas. Read the previous article to understand the context.
Continue reading Excel Tables 10 – Everything is in a name