OR formula 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!