Nested IF formulas are extremely useful for complex decision making on a spreadsheet, but they can also be long, messy and convoluted.
This blog post explores 4 alternatives which are easier, faster and cleaner than the classic nested IF.
Watch the Video
If you use Microsoft Excel on your Mac, you can save the spreadsheets you create and open them in Numbers, Apple's spreadsheet app. It's a handy feature to use in case you can't access Microsoft's. At work I use a Mac and my work IT won't let me install Windows in a virtual machine or BootCamp so I can run this plug-in. Are there any Mac alternatives to Fuzzy Lookup that I. Excel alternative free download - Microsoft Excel 2016, Solver for Excel 2011, Microsoft Office 2011, and many more programs. Easily generate barcodes in Microsoft Excel for Mac 2004 or 2011.
1. The IFS Function (Excel 2016 + only)
Lets start with a function that is new from Excel 2016 called the IFS function.
This function was introduced to condense and simplify the task of writing nested IFs. You can avoid all of those brackets that come with opening and closing multiple IF functions.
In the example below we used an IFS function to calculate the price for each membership type. There are four types of membership (platinum, gold, silver and bronze).
The IFS function tests cell B2 for each membership type and applies the correct price.
2. Using VLOOKUP for an Exact Match
I believe an even better alternative to the IFS function for this scenario would be a lookup.
Now you can use any lookup formula that you like to achieve this. In this example I will use VLOOKUP.
By creating a lookup table (F4:G7 below) we are able to use the following VLOOKUP function to search for each membership and return the correct price.
This is very compact and easier to adapt in the future. If a membership price changes, you can just change the lookup table. There is no need for someone to have to edit the formula, which you or your colleagues may not be comfortable doing 10 months from now.
3. Using VLOOKUP for a Range Lookup
In this alternative formula we use VLOOKUP again, but this time for a range lookup.
Take the example below where we have a list of exam scores and we need to assign a grade for each score.
A nested IF approach could look like this.
Or even like this.
Now these formulas work perfectly. However they are complex and messy. And unless your trying to impress someone with big complex looking functions, there are better ways.
With the lookup table set up in range E4:F9 we could use this VLOOKUP function.
Much simpler.
This is a range lookup, so it is essential that the first column of the lookup table (column E) is in ascending order.
4. The Fantastic CHOOSE Function
This last alternative to the nested IF formula is a bit of a secret function. Many Excel users will never have even heard of the CHOOSE function.
This function will perform an action based on a specific index number. And that makes it perfect for use with form controls.
So in this example I have an interactive chart being driven from a combo box control.
The image below may look a little messy. Typically some of this data would be hidden or stored on separate sheets. It is all on one sheet here to get a better idea of how it works.
The first 7 rows have data of the top goal scorers from 4 different football leagues in the 2016-17 season. A combo box is on the left and is linked to cell A9. A selection from the 4 leagues in the combo box will produce index number 1, 2, 3 or 4 in cell A9.
Formulas are in cells D9 and range D11:E15 returning the correct data for the chart from the combo box selection. This is easier explained in the video.
Now in the cells mentioned above we could use a nested IF like below. This is the nested IF from cell D11 which returns the name of that leagues top goal scorer.
Or we could use this awesome and simple little CHOOSE function. It checks the index value in cell A9 and then returns the relevant information from its list.
I hope you found these nested IF alternatives useful. Check out these other Excel formula tutorials.
Related Posts:
Although Microsoft Office is unarguably the best office suite for any computer, people look for other free or paid alternatives for two main reasons. Microsoft adopted a subscription model and high-price of Office 2019. In addition to the price point, common users, who want to perform basic tasks, like to use free software packages like Google Docs to create, edit, and share documents. No matter where you stand in this situation, you can always check some of the best Microsoft Office alternatives for Mac.
#1. Google Suite
For the last 13 years, G Suite has been providing business solutions in cloud computing, productivity, and collaboration. Since this package includes Google Drives, Mac users can easily create, store, and share their documents from anywhere.
When your team is spread across the length and breadth of the world, this cloud-based software is one of the best alternatives to Microsoft Office for Mac. Apart from PDF and DOC files, you can share your iWork files with others.
Since G Suite boasts minimalist features, it is popular among enterprises. However, some features are deeply rooted in menus; for this reason, G Suite requires some time to explore its full features. If you are familiar with Chrome or Chrome OS, you won't face any challenge in using this package.
Price: Start from $6.00 Per Month
Get G Suite
#2. LibreOffice
LibreOffice can easily fill the vacuum left by Windows and macOS. And one of the first thing it does is to support nearly all document formats. The best thing about LibreOffice is its Office-like interface.
This open-source suite is free; it is primarily known for its word processor, spreadsheet, and presentation software. This software is available in 110 languages.
Unlike Windows Microsoft Office, this does not ask you for frequent updates. A notable feature of this suite is you can export your files into PDF format.
Users, however, complained about the change in fonts and formatting when they export documents from LibreOffice to MS Office. With a smaller download size of 228MB, it is quick to install and use on your Mac. The software is a reliable productivity tool if you are looking for a free alternative to Microsoft Office for Mac.
Price: Free
Get LibreOffice
#3. iWork Suite
iWork mainly involves Pages, Numbers, and Keynote – these three will quickly remind you of Word, Excel, and PowerPoint of Microsoft Office. However, Apple's ecosystem makes the three accessible to all devices that bear this tech giant's name. Thus, you can work in Pages, Numbers, and Keynote on Mac, iPad, and iPhone.
On iPad, you can use Apple Pencil to add illustrations and notations to your documents. iWork is lighter than MS Office, and this is one of the reasons why Mac users avoid using MS Office.
Pages is amazing, in so many words. Especially when I create sensational-looking documents and access the same on non-Apple computers. Yes, Apple lets you use Pages, Numbers, and Keynote on Windows computer using iCloud.
Price: Free
Get iWork Suite
#4. Open Office
Apache Open Office boasts six impressive tools to set up your office in your Mac. This software suite comprises Writer, Calc, Impress, Draw, Base, and Math.
While Writer, Calc, and Impress are lookalikes of Word, Excel, and PowerPoint, the other three have successfully diverged from MS Office. Draw helps you create from simple diagrams to dynamic 3D illustrations.
With Base, you can create and edit tables, forms, queries, and reports. And Math allows you to create mathematical calculations with a graphic user interface.
It is your open source software package, and therefore, you can fix bugs and send your suggestions for improvement. Moreover, it has an active online community, which can help you if you are in a fix.
Price: Free
Get Open Office
#5. FreeOffice
FreeOffice is an easy and free alternative to Microsoft Office. For more than 30 years, SoftMaker has been developing office software, and this is a flagship product. Aside from Windows and Linux, this full-featured suite is compatible with Mac.
Microsoft Excel For Mac Tutorial
You can use TextMaker, PlanMaker, and Presentations the way you use popular Word, Excel, and PowerPoint in Microsoft Office. Since the software is compatible with MS Office, users can save files in formats like DOCX, XLSX, and PPTX.
A noteworthy feature is modern and classic menus and toolbars. You can easily switch between the two as per your requirement or mood.
Price: Free
Get FreeOffice
That's all folks!
Signing off…
LibreOffice is the clear winner for its nearly MS Office like features and appearance. Users are not supposed to update the software frequently. Moreover, you can send and receive documents on Mac as well as other computers. In case you need more information about Microsoft Office alternatives, feel free to shoot us a question in the comment section below.
You may want to take a peek at:
Jignesh Padhiyar is the co-founder of iGeeksBlog.com who has a keen eye for news, rumors and all the unusual stuff that happens around Apple products. During his tight schedule, Jignesh finds some moments of respite to share side-splitting contents on social media.
Word And Excel For Mac
- https://www.igeeksblog.com/author/iosblogger/
- https://www.igeeksblog.com/author/iosblogger/How to Share Purchased Apps Between iPhones and iPads
- https://www.igeeksblog.com/author/iosblogger/
- https://www.igeeksblog.com/author/iosblogger/
