Nested Functions In Excel For Mac

Is there microsoft publisher for mac. A look at nested IF statements and alternatives that are easier to make and understand later. By David Goodmanson In my last article I looked at a plus their useful cousins CountIf and SumIF.

I also touched on the nested IF statement, where there are many IF functions in one cell. This time we’ll look at the nested IF in more detail and especially alternatives that are easier to make and understand later. Following on we’ll look at some alternate ways to utilize the IF statement in some VBA code ( Visual Basic for Applications), as well as the Select Case statement. The rating number in column B is turned into a text description in Column C. In Excel there are all manner of ways to do this. In a simple example like this a nested IF is enough however there are other options that are easier to read by other people.

• Column tab: Column allows you to set column widths, one column at a time. • Options button: Displays the Cell Options dialog. How to do a powerpoint on a mac computer. • Vertical A lignment: Choose Top, Center, or Bottom. • Cell tab: This tab lets you set these properties for the selected cell or range of cells: Here are your options: • Width: Set width precisely by typing a number or using an increase/decrease control.

Excel’s AND function allows you to test for multiple conditions within a single function. Here’s how the formula looks using the AND function instead of two nested IF functions.

No-one ever sets out to make a long group of nested IFs. It usually creeps up on everyone over time. A simple nested IF with 2 or 3 options can grow with more and more IFs to the point where it’s unreadable and prone to coding error.

The function in Column C of the original table now: =VLOOKUP(B3,$G$2:$H$6,2,FALSE) Usually you’d put the lookup table in other tab of the same worksheet but the data can be sourced from anywhere. An alternative is to make a custom VBA function and put the entire IF logic into VBA code.

This has the advantage of being much easier to read and understand. The downside is the need for code security and concern that the VBA code is legitimate and not a virus. If your users are not accustomed to getting worksheets with attached code you might want to avoid VBA options. Here’s a code snippet, it’s the same logic as the nested IF at the start of this article but, as you can see, is a lot easier to read: ' Using a VBA if statement structure ' ================================== If Rating = 5 Then Prediction = 'Boom' ElseIf Rating = 4 Then Prediction = 'Recovery' ElseIf Rating = 3 Then Prediction = 'Turning Point' ElseIf Rating = 2 Then Prediction = 'Recession' ElseIf Rating = 1 Then Prediction = 'Depression' End If VBA: Select Case Finally, an even easier structure to read is the Select Case statement.

'Using a Select Case structure '============================= Select Case Rating Case 5 Prediction = 'Boom' Case 4 Prediction = 'Recovery' Case 3 Prediction = 'Turning Point' Case 2 Prediction = 'Recession' Case 1 Prediction = 'Depression' End Select The Select Case statement is another VBA structure which provides a way of evaluating a range of alternatives with a minimum of repetitive keying required. It is also simpler and easier to read and should be used when there are more than 3 alternatives of the variable of interest. VBA: and beyond Finally to give you some idea of the greater power of Select Case here’s an extended version of Select Case: Select Case Rating Case 4.5 To 10 Prediction = 'Boom' Case 3.5 To 4.49 Prediction = 'Recovery' Case 2.5 To 3.49 Prediction = 'Turning Point' Case 1.5 To 2.49 Prediction = 'Recession' Case 0.5 To 1.4 Prediction = 'Depression' Case 0 To 0.49 Prediction = 'Money under the Mattress' Case Else Prediction = 'No prediction - there's no economy left!' End Select This version tests for ranges of values (eg.

A rating from more than or equal to 3.5 but less than or equal to 4.5) and allows for someone offering a fractional rating like 2.5. In addition, at the bottom of the structure is the “Else” control. Basically it covers everything else we didn’t cover in our preceding Elseifs.

If nothing equates to the rating, then the Else part of the structure provides an alternative control. Well that’s it for the IF function series. Keep your feedback, questions and suggestions coming in. See you next time. See Also • • • • •.