I'm working on an excel spreadsheet and I'm having trouble creating a formula. For someone that uses excel a lot, I'm sure it's easy. Let's see if I can explain...I'm trying to tell cell #1 that IF cell #2 is higher than cell #3 AND cell #4 is higher than value 100 use cell #5, higher than 250 use cell #6, and if higher than 400 use cell #7.
I have attached the file, let's see if I can explain more.
My employees are paid bonus if they beat the goal and have a minimum of 100 Customer Count (CC). Once those two are met, they are paid based on one of three tiers (found on "Categories" tab). The more CC, the more they earn for that category. The sheet is used to calculate Bonus for sales earned. I'm trying to do the formulas on the PAYOUT tab. The requirement for each category (ie. AF, ATS, Etc..) is the quantity sold (found on "All Employee" tab) must be higher than the Goal (on "Categories" tab) AND if "CC" is 100-249 then they get paid from column M on tab CATEGORIES, if 250 - 399, then they get paid from column N, and if 400 or more they get paid from column O.
I understand the organization of the data, sorry for that. First, I truly appreciate your response, thank you so much. However, this did not work. If you look on tab ALL EMPLOYEES you will see that "Additives" were not hit as the goal was 3 but the additives sold were 0. On the PAYOUT tab he should not have received $25.
To simplify I took data from related All Employee cell (much better INDEX/MATCH employees by ID or another unique field) and took Goal from Pay Out tab (Goals are repeating here from Categories, better to lookup them based on category abbreviation).