Using the OR function in Excel
OR function in Excel is pretty useless without combining, or rather nesting, it inside an IF function. This way you can customise your IF formula to give sales teams a bonus if they reach their sales target for at least one month during the first quarter.
- Add the Text Sales Teams in Cell A1
- Then Type in Team 1 and Team 2 in Cells A2,A3.
- Autofill down to Team 6.
- Add in the Months Jan to Feb from B1 to D1
- In Cell B2 enter =RANDBETWEEN(100, 1000) to create random numbers from 100 to 1000.
- Autofill to the right and then down to fill in the numbers.
- Select all the numbers then Copy and Paste Values to remove the RANDBETWEEN formulas so the numbers are consistent. Now it's time to work out who gets the bonuses
- Type Bonus in E1.
- Click in E2 and type the following:
- =OR(B2>=700, C2>=700, D2>=700) alternatively you can use the mouse to click on the cells you want to include in your formula. In the video I use the insert function button to write the formula. This way might make it a little easier to write the function.
- Press Enter and notice the first result. Remember your results may be different.
- Autofill down.
- Double click the OR statement in Cell E2 and highlight then copy everything except for the equals sign at the beginning.
- Click fx Insert function button and search for the IF function.
- Double click on the IF function
- Paste the copied OR formula into the Logical test box.
- In the value if True box type: "Bonus Received - Woohoo!"
(Excel will automatically add in the quotation marks)
- In the value if False box type: "No Bonus - Too Bad!"
- Click OK.
- Autofill down.
There you have it! I think you'll agree quite a powerful function. There will be more!