Microsoft Office Forums IF formula - multiple conditions
 Register FAQ Search Today's Posts Mark Forums Read

#1
02-10-2019, 06:50 PM
 rameen Windows XP Office 2016 Novice Join Date: Feb 2019 Posts: 1
IF formula - multiple conditions

Hi,

I am trying to calculate the following using an if formula:

Sales GP% Bonus
50% 90% 2%
60% 95% 3%

How can I use the above data to calculate the Bonus amount in \$, when (condition 1) sales are higher than 50% but lower than 60% and (condition 2) GP % is higher than 90% but lower than 95%. Bonus percentage needs to be picked according to these conditions so from the above data, there could be 4 possible combinations for the formula to look at.

Thanks.

Rameen.
#2
02-11-2019, 12:37 AM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 467

Into your table with data, add a column (may be hidden) , where depending on sales and GP % the bonus group number (values 1, 2, 3 or 4) for every row is calculated.
The bonus will be calculated as
Code:
`=CHOOSE(BonusGroupNumber, Bonus1, Bonus2, Bonus3, Bonus4)`
where Bonus# are numeric values for different bonuses like 0.01, 0.03, etc.

 Tags if formula

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post allex011 Excel 1 10-29-2018 03:22 AM ilsel Excel 3 11-15-2017 02:03 AM nowco Excel 4 03-16-2016 11:24 AM z941714 Excel 8 08-09-2015 04:12 PM paconovellino Excel 2 03-05-2012 06:45 AM

All times are GMT -7. The time now is 02:04 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top