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
Readable formulas in Excel tables are so nice, you will curse yourself for not noticing earlier (since 8 years!). Teach this to everyone around you – NOW! Show off
Continue reading Excel Tables 9 – Readable Formulas in Calculated Columns
Most probably, you have been missing this Auto-copy feature for the last 8 years. Take 3 minutes to find out how it can simplify your life and increase accuracy.
Continue reading Excel Tables 6 – Miraculous Formula Auto-Copy
The Need: Trace Dependents
Excel workbooks are complex. Multiple persons share and handle the same file. We keep mailing them to each other. Often the person who created the file has left the organization long back. There is never any documentation available about exactly how a file works.
Due to all this, it is almost impossible to remember which value is used in which formula. When you are about to change a value or add more data to existing block, it is absolutely necessary to ensure that all the related formulas are updated. If you forget to update the formula, the results shown there will be outdated and WRONG. This can hamper decisions and can have severe side effects.
What we need is a way to find out which formulas to update when any data changes or grows. Excel has provided this facility since 25 years. Unfortunately, very few people are even aware of it – leave alone using it!
Continue reading Excel – Impact Analysis – Trace Dependents
Yes, you have been copying formulas for decades. I know. But you don’t know the most efficient method – most probably! Try it out. Spend just 4 minutes of your time.
Continue reading Efficiency Test 6: Excel : Copying Formulas
Did you know Word can contain formulas? Yes. It can.
For simple calculations you don’t need to use Excel.
Estimated learning time 5 min
Continue reading Using Formulas in Word tables