Conditional formula
In the table below I would like to get a summary table by applying a formula based on the several conditions.They are
IF all the three (Self, Internal and External) are present then the Formula should be(20%Self+10%Internal+30%External)
IF only External and Internal are present then the formula should be (70%external+ 30% internal)
If only External and self are present then the formula should be (70% external+30% Internal)
If only Internal and self are present then the formula should be (30% Internal+70% self)
If only one type of Nomination (Self, Internal or External) if present then. Apply 100% of that.
Employee ID Nomination Part 1 Part 2 Part 3 Part 4 Part 5 Part 6 Part 7
1 Self 4.0 2.0 4.0 4.0
1 Internal 2.0 2.0 4.0 3.0
1 External 3.0 2.0 1.0 2.0
result 3.1 2.0 2.0 2.0 1.9 2.6 3.7
2 Self 2.0 4.0 3.0 5.0
2 Internal 3.0 3.0 6.0 2.0
2 External 5.0 2.0 2.0 2.0
result 0.3 4.2 3.2 2 4 2.3 4.1
Employee ID Summary
1 3.1 2 2 2 1.9 2.6 3.7
2 0.3 4.2 3.2 2 4 2.3 4.1
| Attachment | Size |
|---|---|
| Book1.xlsx | 16.13 KB |
use this formula
put this in C5 and copy accross:
=IF(COUNT(C2:C4)=3,ROUND(0.2*C2+0.1*C3+0.7*C4,1),IF(AND(COUNT(C2:C4)=2,C3=""),ROUND(0.3*C2+0.7*C4,1),IF(AND(COUNT(C2:C4)=2,C2=""),ROUND(0.3*C3+0.7*C4,1),IF(COUNT(C2:C4)=2,ROUND(0.3*C3+0.7*C2,1),SUM(C2:C4)))))
I have put the round function in to ensure the value is to 1 decimal place, feel free to remove this as it doesn't effect anything else
Regards
Mark