Hi everyone, I'm assigned to do this "Employee Optimization" project at office and I'm stuck with some excel formula that I've made.
But first, let me explain the conditions that had been set in the formula to help you guys understand the formula better.
1. the starting performance rate is 0.50. the calculation period starts from March to August with January data as Anchor point (March compare with January, April compare with January, and so on)
2. Two months equal 1 term. Total of 3 terms. Finishing 1st term, performance rate +0.25. Finishing 2nd term, performance rate +0.50. Finishing 3rd terms, performance rate +0.75. In this case if department can finish 3 terms, means the starting performance rate of 0.50 will become 1.25.
3. Each department has different optimization percentage. some has 5%, 5%, 5% in each terms, some has 5%, 10%, 10%.
4. If department finish any terms, the performance rate will be adjusted accordingly. For example, Dept A's total employee number on March is 95 persons, less 5 persons comparing to January's total number of 100. In this case Dept A finish the 1st term. therefore, March's performance rate will be adjusted from 0.5 to 0.75 (+0.25)
5. The number of optimized employees in the department is calculated by rounding up, for example: 15% of 10 persons is 1.5, means there will be 2 persons need to be optimized.
6. Employee numbers are divided by employee who receive salary more than/equal to $1,000 and employee who receive salary lesser than $1,000. If the manager of a department decides to optimize those who receive salary lesser than $1,000, then there is a different set of rules applied for it. 2 employees equal 1, 3 employees equal 2. Here I provide the table explaining the rules.
7. If a department can't finish 1st term of optimization in 3 months, the performance rate will be deducted by 0.25. If can't finish 1st term in 6 months, it will be deducted by another 0.25
8. If a department finish 3 terms and the number of employees after optimization is stable for 3 months, then the performance rate of 1.25 will be permanent.
9. If department can additionally optimize its employee number by 10% from January total number of employee (after the 3 terms), then the performance rate will add 0.50 (this additional 10% we called it 4th term). If stable for 3 months, then the performance rate will be permanent (in this case, 1.25 + 0.50 = 1.75)
My problem here is at the point number 6, where somehow I can't get the right result of performance rate when tweaking with <$1,000 numbers.
For example, Dept B has total employee of 110 (≥$1,000: 100 persons, <$1,000 10 persons), with optimization rate for 3 terms are 5%, 10%, 10%. So Dept B need to optimize 28 persons. Let's say that Dept B wants to finish the first term, which is 5%, 110 persons * 5% = 5.5~6 persons. If I put the number of employee in March with 94 + 10, it shows a correct result, which is 0.75. But, if I put 100 + 1, it shows 0.50 as result, which is wrong. Logically, if Dept B optimizes employee who receives <$1,000 by 9 persons, with the rules of 3 equals 2, 9 persons should equal 6 persons, therefore the performance rate should be 0.75.
Sorry for the long explanation, but can someone help me figuring out what's wrong with my formula?
Here attached the mini sheet of my table. Thanks!
But first, let me explain the conditions that had been set in the formula to help you guys understand the formula better.
1. the starting performance rate is 0.50. the calculation period starts from March to August with January data as Anchor point (March compare with January, April compare with January, and so on)
2. Two months equal 1 term. Total of 3 terms. Finishing 1st term, performance rate +0.25. Finishing 2nd term, performance rate +0.50. Finishing 3rd terms, performance rate +0.75. In this case if department can finish 3 terms, means the starting performance rate of 0.50 will become 1.25.
3. Each department has different optimization percentage. some has 5%, 5%, 5% in each terms, some has 5%, 10%, 10%.
4. If department finish any terms, the performance rate will be adjusted accordingly. For example, Dept A's total employee number on March is 95 persons, less 5 persons comparing to January's total number of 100. In this case Dept A finish the 1st term. therefore, March's performance rate will be adjusted from 0.5 to 0.75 (+0.25)
5. The number of optimized employees in the department is calculated by rounding up, for example: 15% of 10 persons is 1.5, means there will be 2 persons need to be optimized.
6. Employee numbers are divided by employee who receive salary more than/equal to $1,000 and employee who receive salary lesser than $1,000. If the manager of a department decides to optimize those who receive salary lesser than $1,000, then there is a different set of rules applied for it. 2 employees equal 1, 3 employees equal 2. Here I provide the table explaining the rules.
| Optimization and Performance rate.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Rules | Dept | Total no. of EE | ≥$1,000 | Total No. of optimization | Actual No. of Optimization | Remaining No. of EE | ||
| 2 | Optimization Percentage | <$1,000 | |||||||
| 3 | 2 equals 1 | A | 120 | 118 | 30 | 29 | 89 | ||
| 4 | 25% | 2 | 2 | ||||||
| 5 | B | 104 | 102 | 26 | 25 | 77 | |||
| 6 | 25% | 2 | 2 | ||||||
| 7 | 3 equals 2 | C | 75 | 71 | 11 | 9 | 63 | ||
| 8 | 15% | 4 | 3 | ||||||
| 9 | D | 59 | 46 | 9 | 5 | 48 | |||
| 10 | 15% | 13 | 6 | ||||||
2=1 3=2 | |||||||||
7. If a department can't finish 1st term of optimization in 3 months, the performance rate will be deducted by 0.25. If can't finish 1st term in 6 months, it will be deducted by another 0.25
8. If a department finish 3 terms and the number of employees after optimization is stable for 3 months, then the performance rate of 1.25 will be permanent.
9. If department can additionally optimize its employee number by 10% from January total number of employee (after the 3 terms), then the performance rate will add 0.50 (this additional 10% we called it 4th term). If stable for 3 months, then the performance rate will be permanent (in this case, 1.25 + 0.50 = 1.75)
My problem here is at the point number 6, where somehow I can't get the right result of performance rate when tweaking with <$1,000 numbers.
For example, Dept B has total employee of 110 (≥$1,000: 100 persons, <$1,000 10 persons), with optimization rate for 3 terms are 5%, 10%, 10%. So Dept B need to optimize 28 persons. Let's say that Dept B wants to finish the first term, which is 5%, 110 persons * 5% = 5.5~6 persons. If I put the number of employee in March with 94 + 10, it shows a correct result, which is 0.75. But, if I put 100 + 1, it shows 0.50 as result, which is wrong. Logically, if Dept B optimizes employee who receives <$1,000 by 9 persons, with the rules of 3 equals 2, 9 persons should equal 6 persons, therefore the performance rate should be 0.75.
Sorry for the long explanation, but can someone help me figuring out what's wrong with my formula?
Here attached the mini sheet of my table. Thanks!| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E9,G9 | E9 | =IFERROR(IF((E7-ROUNDUP(($C$8-E8)/2,0))<$C$7-ROUND(($C$7+$C$8)*25%,0)+1,$C$9+1.25,IF((E7-ROUNDUP(($C$8-E8)/2,0))<$C$7-ROUND(($C$7+$C$8)*15%,0)+1,$C$9+0.75,IF((E7-ROUNDUP(($C$8-E8)/2,0))<$C$7-ROUND(($C$7+$C$8)*10%,0)+1,$C$9+0.5,IF((E7-ROUNDUP(($C$8-E8)/2,0))<$C$7-ROUND(($C$7+$C$8)*5%,0)+1,$C$9+0.25,$C$9))))," ") |
| I9,O9 | I9 | =IFERROR(IF(OR(AND((E7-ROUNDUP(($C$8-E8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*5%,0)+1,(G7-ROUNDUP(($C$8-G8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*5%,0)+1,(I7-ROUNDUP(($C$8-I8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*5%,0)+1),AND((E7-ROUNDUP(($C$8-E8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*10%,0)+1,(G7-ROUNDUP(($C$8-G8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*10%,0)+1,(I7-ROUNDUP(($C$8-I8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*10%,0)+1,(E7-ROUNDUP(($C$8-E8)/2,0))<$C$7-ROUND(($C$7+$C$8)*5%,0)+1,(G7-ROUNDUP(($C$8-G8)/2,0))<$C$7-ROUND(($C$7+$C$8)*5%,0)+1,(I7-ROUNDUP(($C$8-I8)/2,0))<$C$7-ROUND(($C$7+$C$8)*5%,0)+1),AND(AND((E7-ROUNDUP(($C$8-E8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*15%,0)+1,(G7-ROUNDUP(($C$8-G8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*15%,0)+1,(I7-ROUNDUP(($C$8-I8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*15%,0)+1,(E7-ROUNDUP(($C$8-E8)/2,0))<$C$7-ROUND(($C$7+$C$8)*10%,0)+1,(G7-ROUNDUP(($C$8-G8)/2,0))<$C$7-ROUND(($C$7+$C$8)*10%,0)+1,(I7-ROUNDUP(($C$8-I8)/2,0))<$C$7-ROUND(($C$7+$C$8)*10%,0)+1),AND((E7-ROUNDUP(($C$8-E8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*15%,0),(G7-ROUNDUP(($C$8-G8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*15%,0),(I7-ROUNDUP(($C$8-I8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*15%,0)))),G9,IF((I7-ROUNDUP(($C$8-I8)/2,0))<$C$7-ROUND(($C$7+$C$8)*25%,0)+1,$C$9+1.25,IF((I7-ROUNDUP(($C$8-I8)/2,0))<$C$7-ROUND(($C$7+$C$8)*15%,0)+1,$C$9+0.75,IF((I7-ROUNDUP(($C$8-I8)/2,0))<$C$7-ROUND(($C$7+$C$8)*10%,0)+1,$C$9+0.5,IF((I7-ROUNDUP(($C$8-I8)/2,0))<$C$7-ROUND(($C$7+$C$8)*5%,0)+1,$C$9+0.25,$C$9)))))-IF(OR(AND((E7-ROUNDUP(($C$8-E8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*5%,0)+1,(G7-ROUNDUP(($C$8-G8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*5%,0)+1,(I7-ROUNDUP(($C$8-I8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*5%,0)+1),AND((E7-ROUNDUP(($C$8-E8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*10%,0)+1,(G7-ROUNDUP(($C$8-G8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*10%,0)+1,(I7-ROUNDUP(($C$8-I8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*10%,0)+1,(E7-ROUNDUP(($C$8-E8)/2,0))<$C$7-ROUND(($C$7+$C$8)*5%,0)+1,(G7-ROUNDUP(($C$8-G8)/2,0))<$C$7-ROUND(($C$7+$C$8)*5%,0)+1,(I7-ROUNDUP(($C$8-I8)/2,0))<$C$7-ROUND(($C$7+$C$8)*5%,0)+1),AND(AND((E7-ROUNDUP(($C$8-E8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*15%,0)+1,(G7-ROUNDUP(($C$8-G8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*15%,0)+1,(I7-ROUNDUP(($C$8-I8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*15%,0)+1,(E7-ROUNDUP(($C$8-E8)/2,0))<$C$7-ROUND(($C$7+$C$8)*10%,0)+1,(G7-ROUNDUP(($C$8-G8)/2,0))<$C$7-ROUND(($C$7+$C$8)*10%,0)+1,(I7-ROUNDUP(($C$8-I8)/2,0))<$C$7-ROUND(($C$7+$C$8)*10%,0)+1),AND((E7-ROUNDUP(($C$8-E8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*15%,0),(G7-ROUNDUP(($C$8-G8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*15%,0),(I7-ROUNDUP(($C$8-I8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*15%,0)))),0.25,0)," ") |
| K9,M9 | K9 | =IFERROR(IF(OR(AND((G7-ROUNDUP(($C$8-G8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*5%,0)+1,(I7-ROUNDUP(($C$8-I8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*5%,0)+1,(K7-ROUNDUP(($C$8-K8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*5%,0)+1),AND((G7-ROUNDUP(($C$8-G8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*10%,0)+1,(I7-ROUNDUP(($C$8-I8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*10%,0)+1,(K7-ROUNDUP(($C$8-K8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*10%,0)+1,(G7-ROUNDUP(($C$8-G8)/2,0))<$C$7-ROUND(($C$7+$C$8)*5%,0)+1,(I7-ROUNDUP(($C$8-I8)/2,0))<$C$7-ROUND(($C$7+$C$8)*5%,0)+1,(K7-ROUNDUP(($C$8-K8)/2,0))<$C$7-ROUND(($C$7+$C$8)*5%,0)+1),AND(AND((G7-ROUNDUP(($C$8-G8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*15%,0)+1,(I7-ROUNDUP(($C$8-I8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*15%,0)+1,(K7-ROUNDUP(($C$8-K8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*15%,0)+1,(G7-ROUNDUP(($C$8-G8)/2,0))<$C$7-ROUND(($C$7+$C$8)*10%,0)+1,(I7-ROUNDUP(($C$8-I8)/2,0))<$C$7-ROUND(($C$7+$C$8)*10%,0)+1,(K7-ROUNDUP(($C$8-K8)/2,0))<$C$7-ROUND(($C$7+$C$8)*10%,0)+1),AND((G7-ROUNDUP(($C$8-G8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*15%,0),(I7-ROUNDUP(($C$8-I8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*15%,0),(K7-ROUNDUP(($C$8-K8)/2,0))>=$C$7-ROUND(($C$7+$C$8)*15%,0)))),I9,IF((K7-ROUNDUP(($C$8-K8)/2,0))<$C$7-ROUND(($C$7+$C$8)*25%,0)+1,$C$9+1.25,IF((K7-ROUNDUP(($C$8-K8)/2,0))<$C$7-ROUND(($C$7+$C$8)*15%,0)+1,$C$9+0.75,IF((K7-ROUNDUP(($C$8-K8)/2,0))<$C$7-ROUND(($C$7+$C$8)*10%,0)+1,$C$9+0.5,IF((K7-ROUNDUP(($C$8-K8)/2,0))<$C$7-ROUND(($C$7+$C$8)*5%,0)+1,$C$9+0.25,$C$9)))))," ") |
| E12,G12 | E12 | =IFERROR(IF((E10-ROUNDUP(($C$11-E11)/2,0))<$C$10-ROUND(($C$10+$C$11)*35%,0)+1,$C$12+1.25,IF((E10-ROUNDUP(($C$11-E11)/2,0))<$C$10-ROUND(($C$10+$C$11)*25%,0)+1,$C$12+0.75,IF((E10-ROUNDUP(($C$11-E11)/2,0))<$C$10-ROUND(($C$10+$C$11)*15%,0)+1,$C$12+0.5,IF((E10-ROUNDUP(($C$11-E11)/2,0))<$C$10-ROUND(($C$10+$C$11)*5%,0)+1,$C$12+0.25,$C$12))))," ") |
| I12,O12 | I12 | =IFERROR(IF(OR(AND((E10-ROUNDUP(($C$11-E11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*5%,0)+1,(G10-ROUNDUP(($C$11-G11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*5%,0)+1,(I10-ROUNDUP(($C$11-I11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*5%,0)+1),AND((E10-ROUNDUP(($C$11-E11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*15%,0)+1,(G10-ROUNDUP(($C$11-G11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*15%,0)+1,(I10-ROUNDUP(($C$11-I11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*15%,0)+1,(E10-ROUNDUP(($C$11-E11)/2,0))<$C$10-ROUND(($C$10+$C$11)*5%,0)+1,(G10-ROUNDUP(($C$11-G11)/2,0))<$C$10-ROUND(($C$10+$C$11)*5%,0)+1,(I10-ROUNDUP(($C$11-I11)/2,0))<$C$10-ROUND(($C$10+$C$11)*5%,0)+1),AND(AND((E10-ROUNDUP(($C$11-E11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*25%,0)+1,(G10-ROUNDUP(($C$11-G11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*25%,0)+1,(I10-ROUNDUP(($C$11-I11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*25%,0)+1,(E10-ROUNDUP(($C$11-E11)/2,0))<$C$10-ROUND(($C$10+$C$11)*15%,0)+1,(G10-ROUNDUP(($C$11-G11)/2,0))<$C$10-ROUND(($C$10+$C$11)*15%,0)+1,(I10-ROUNDUP(($C$11-I11)/2,0))<$C$10-ROUND(($C$10+$C$11)*15%,0)+1),AND((E10-ROUNDUP(($C$11-E11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*25%,0),(G10-ROUNDUP(($C$11-G11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*25%,0),(I10-ROUNDUP(($C$11-I11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*25%,0)))),G12,IF((I10-ROUNDUP(($C$11-I11)/2,0))<$C$10-ROUND(($C$10+$C$11)*35%,0)+1,$C$12+1.25,IF((I10-ROUNDUP(($C$11-I11)/2,0))<$C$10-ROUND(($C$10+$C$11)*25%,0)+1,$C$12+0.75,IF((I10-ROUNDUP(($C$11-I11)/2,0))<$C$10-ROUND(($C$10+$C$11)*15%,0)+1,$C$12+0.5,IF((I10-ROUNDUP(($C$11-I11)/2,0))<$C$10-ROUND(($C$10+$C$11)*5%,0)+1,$C$12+0.25,$C$12)))))-IF(OR(AND((E10-ROUNDUP(($C$11-E11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*5%,0)+1,(G10-ROUNDUP(($C$11-G11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*5%,0)+1,(I10-ROUNDUP(($C$11-I11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*5%,0)+1),AND((E10-ROUNDUP(($C$11-E11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*15%,0)+1,(G10-ROUNDUP(($C$11-G11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*15%,0)+1,(I10-ROUNDUP(($C$11-I11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*15%,0)+1,(E10-ROUNDUP(($C$11-E11)/2,0))<$C$10-ROUND(($C$10+$C$11)*5%,0)+1,(G10-ROUNDUP(($C$11-G11)/2,0))<$C$10-ROUND(($C$10+$C$11)*5%,0)+1,(I10-ROUNDUP(($C$11-I11)/2,0))<$C$10-ROUND(($C$10+$C$11)*5%,0)+1),AND(AND((E10-ROUNDUP(($C$11-E11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*25%,0)+1,(G10-ROUNDUP(($C$11-G11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*25%,0)+1,(I10-ROUNDUP(($C$11-I11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*25%,0)+1,(E10-ROUNDUP(($C$11-E11)/2,0))<$C$10-ROUND(($C$10+$C$11)*15%,0)+1,(G10-ROUNDUP(($C$11-G11)/2,0))<$C$10-ROUND(($C$10+$C$11)*15%,0)+1,(I10-ROUNDUP(($C$11-I11)/2,0))<$C$10-ROUND(($C$10+$C$11)*15%,0)+1),AND((E10-ROUNDUP(($C$11-E11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*25%,0),(G10-ROUNDUP(($C$11-G11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*25%,0),(I10-ROUNDUP(($C$11-I11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*25%,0)))),0.25,0)," ") |
| K12,M12 | K12 | =IFERROR(IF(OR(AND((G10-ROUNDUP(($C$11-G11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*5%,0)+1,(I10-ROUNDUP(($C$11-I11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*5%,0)+1,(K10-ROUNDUP(($C$11-K11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*5%,0)+1),AND((G10-ROUNDUP(($C$11-G11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*15%,0)+1,(I10-ROUNDUP(($C$11-I11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*15%,0)+1,(K10-ROUNDUP(($C$11-K11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*15%,0)+1,(G10-ROUNDUP(($C$11-G11)/2,0))<$C$10-ROUND(($C$10+$C$11)*5%,0)+1,(I10-ROUNDUP(($C$11-I11)/2,0))<$C$10-ROUND(($C$10+$C$11)*5%,0)+1,(K10-ROUNDUP(($C$11-K11)/2,0))<$C$10-ROUND(($C$10+$C$11)*5%,0)+1),AND(AND((G10-ROUNDUP(($C$11-G11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*25%,0)+1,(I10-ROUNDUP(($C$11-I11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*25%,0)+1,(K10-ROUNDUP(($C$11-K11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*25%,0)+1,(G10-ROUNDUP(($C$11-G11)/2,0))<$C$10-ROUND(($C$10+$C$11)*15%,0)+1,(I10-ROUNDUP(($C$11-I11)/2,0))<$C$10-ROUND(($C$10+$C$11)*15%,0)+1,(K10-ROUNDUP(($C$11-K11)/2,0))<$C$10-ROUND(($C$10+$C$11)*15%,0)+1),AND((G10-ROUNDUP(($C$11-G11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*25%,0),(I10-ROUNDUP(($C$11-I11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*25%,0),(K10-ROUNDUP(($C$11-K11)/2,0))>=$C$10-ROUND(($C$10+$C$11)*25%,0)))),I12,IF((K10-ROUNDUP(($C$11-K11)/2,0))<$C$10-ROUND(($C$10+$C$11)*35%,0)+1,$C$12+1.25,IF((K10-ROUNDUP(($C$11-K11)/2,0))<$C$10-ROUND(($C$10+$C$11)*25%,0)+1,$C$12+0.75,IF((K10-ROUNDUP(($C$11-K11)/2,0))<$C$10-ROUND(($C$10+$C$11)*15%,0)+1,$C$12+0.5,IF((K10-ROUNDUP(($C$11-K11)/2,0))<$C$10-ROUND(($C$10+$C$11)*5%,0)+1,$C$12+0.25,$C$12)))))," ") |
