Hi,

I have been spending much time on working with IF functions to generate a bonus based on a few conditions. I have tried compiling a complex if function into ONE column but it doesn't pull the data when I drag the formula down and into a cell where a bonus should be given.

Here is the spreadsheet I am working with Paying a Bonus.xlsx

Functions List_Question.JPG

=IF($I14="Quebec",IF($O14<'Bonus Pay'!$B$3,"no bonus",IF($O14<'Bonus Pay'!$B$4,'Bonus Pay'!$A$3,'Bonus Pay'!$A$4)),IF(I14="Alberta, British Colombia, Manitoba, New Foundland, Nova Scotia, Ontario, Saskatchewan",IF($O14<'Bonus Pay'!$E$3,"no bonus",IF($O14<'Bonus Pay'!$E$4,'Bonus Pay'!$D$3,IF($O14<'Bonus Pay'!$E$5,'Bonus Pay'!$D$4,'Bonus Pay'!$D$5)))))

I am not familiar with what overlapping function to use (is this a IF AND function, IF NOT function (to condense the provinces to "not Quebec"))

My conditions are as follows:

For Quebec:
If sales/hr < 90, no bonus
If sales/hr > = 90, pay $25 bonus
If sales/hr > = 160, pay $50 bonus

For the Rest of Canada:
If sales/hr < 150, no bonus
If sales/hr >= 150, pay $25 bonus
If sales/hr >= 225, pay $50 bonus
If sales/hr >= 325, pay $100 bonus

For London Drugs (banner column)
If sales/hr < 125, no bonus
If sales/hr >= 125, pay $25 bonus
If sales/hr >= 200, pay $50 bonus
If sales/hr >= 300, pay $100 bonus

If someone can point me in the right direction on how to write this and even if the above is partially right i am not sure where i am messing up the rest. I probably lack some basic excel knowledge that is throwing me off too. I'd really appreciate it if someone can help me get this more specific (I'm open to new ideas on how to make it cleaner as well).

Thank you