Using IF with AND, OR and NOT functions
※ Download: If or statement excel
We will assume that your number 'n' resides in cell B1. If either condition is met, the bonus is given.
You can nest up to 7 to create a complex IF THEN ELSE statement. You can nest up to seven IF functions.
Using IF with AND, OR and NOT functions - Even in Excel 2003, there is no indication that the problem with the formula is too many IFs. Don't need any special skills, save two hours every day!
The original question I received from a reader, which prompted this series, was about how to use the IF statement in Excel when you had two conditions that had to be met. For example, when sales fell between a minimum and maximum number. First, open up a blank excel worksheet. Next, highlight the table below. Copy it, and then go back to your excel worksheet. The data should appear in your Excel worksheet just as it does above. Bonus 12% Minimum amount 100000 Maximum amount 200000 Sales Bonus Salesman A 87925 Salesman B 100000 Salesman C 145000 Salesman D 200750 Salesman E 178650 Salesman F 99555 Salesman G 147000 Salesman H 213450 Salesman I 122680 Salesman J 92500 Ok, now back to the tutorial. It turns out that you can use , which Excel calls a logical operator just like it calls the IF function. And, as usual, unlike how most other programming languages work, the syntax required in Excel is a bit different. In other programming languages, AND would fall in between each condition, just like how we normally talk, but not in Excel! By the way, in our case, we only had two conditions to meet, but if we had more, we could just keep adding them into the list of conditions in the parenthese after the AND function. Otherwise, multiply B6 by B1. The other problem is usability.
When you do this you can omit the IF function and use AND, OR and NOT on their own. You'll also learn how to troubleshoot, trace errors, and fix problems. You may only use 7 nested IF statements. If the prices change, just modify the matrix at the top of the spreadsheet, and all the other formulas and totals will automatically adjust. You could perform entirely different calculations based on the output of some other calculation. Excel has a VBA backend called the VB editor. Column Y is my set hour reset.
