How to Calculate % based on number of Months is less than 12

Thank you for answering, I am not sure I fully understand. Is there a way you can break that down a little?

My formula for averages is correct, however I removed 3 columns of employee names for privacy and never fixed the formula.

azTEPHY,

Hopefully this will clarify it. YOur formula has to cover six different situations:

1. Employee has worked for the company for more than a year AND averaged more than 100%:
Col B >(Col A + 1 year), Col G >100%: What bonus?
If the date in Col B is more than one year AFTER their Start Date in Col A, AND their average in Col G is MORE than 100% the employee will get a "Higher" bonus.
But there is nothing about this in the formula. How will that bonus be worked out? Will it be a 'flat' Rate (e.g. 6%)? Or a "scale", linked to their average Score, so someone averaging 125% will get 4.5% x 1.25 (5.625%) whilst someone averaging 110% will get 4.5 x 1.1 (4.95%). Or something different again?

2. Employee has worked for the company for more than a year AND averages 100%:
Col B >(Col A + 1 year), Col G =100%: Formula says 4.5% bonus
Formula says if the date in Col B is more than one year AFTER their Start Date in Col A, AND their average in Col G is 100%, AND their average in Col G is MORE than 100% the employee will get a 4.5% bonus.

3. Employee has worked for the company for more than a year BUT does not average 100%:
Col B >(Col A + 1 year), Col G < 100%. What bonus?
If the date in Col B is more than one year AFTER their Start Date in Col A, BUT they do not average 100% in Col G, do they get any bonus?
And if they do, how will it be calculated? Will it be a 'flat' Rate (e.g. 3%)? Or a "scale" linked to their average Score, so someone averaging 75% will get 4.5% x 0.75 (3.375%) whilst someone averaging 50% will get 4.5 x 0,5 (2%). Or something different again?


4. Employees has not been with the company for more than a year BUT gets more than 100% average:
Col B <(Col A + 1 year), Col G >100%: What bonus %?
All you say is if an employee has not been employed for a whole year (Date in Col B is LESS than a year after the date in Col A), they will not get the ‘full’ raise. No mention of the average score.
Will the bonus be linked to the "higher" level awarded to those with more than one years' service (e.g. 6%) which is reduced in some way to reflect the shorter employment period?
Or will it link to both their average Score AND the percentage of the year they have been employed?
(E.g. you worked for six months and averaged 125%, so you get 4.5% x 1.25 x 0.5 (2.812%) whilst I averaged 110% so I get 4.5 x 1.1 x 0.5 (2.475%). Or something different again?

5. Employees not with the company for more than a year but achieving the 100% average:
Col B ,(Col A + 1 year), Col G =100%: What bonus?
All you say is if an employee has not been employed for a whole year (Date in Col B is LESS than a year after the date in Col A), they will not get the ‘full’ raise.
What will that ‘reduced’ level be? Or how it will be worked out? Will it be 4.5% divided by the percentage of the year they have been employed? Or something different again?

5. Employees not with the company for more than a year AND missing the 100% average:
Col B <(Col A + 1 year), Col G < 100%: What bonus?
Will someone employed for less than a year and a day still get a bonus if they do NOT average 100%? And if so, how will it be calculated? Will it be a 'flat' Rate (e.g. 3%)? Or a "scale", linked to their average Score, so someone employed for six months averaging 75% will get 4.5% x 0.75 x 0.5 (1.688%) whilst someone averaging 50% will get 4.5 x 0,5 x 0,5 (1.125%). Or something different again?

Ochimus

Ok, I am still lost, will something like this work?

=(DATEDIF(D2,E2,"ym"))<"y"/12*L2

I attached a new work book so you can see what is going on.

Stephy,

The formula you suggests will not cover the six different conditions you need.

I am sorry you could not follow the description I gave you last week, but hopefully you will be able to follow the problem through the attached.

It shows in detail three employees who have worked for more than one year, and either exceeded, met or did not meet the 100% average, and three employees who have worked for LESS than a year, who either exceeded, met or did not meet the 100% average.

It will help you decide in each case (a) whether a bonus should be paid at all to someone (e.g. Employee no. 6 has not been employed for the full year AND did not achieve 100% average) OR how you will determine what bonus % they should receive (e.g. Employee 4 has not worked with the company for the full year BUT averaged MORE than 100%).

Once you decide, you can construct the single formula that will take all six "scenarios" into account automatically.

Ochimus