Katherine82
asked on
Match formula when arrays are not identical
I need to add multiple conditions and looking for a formula that summarizes the result. Please see the excel spreadsheet attached.
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%
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
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%
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
there's no file attached...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
ASKER
IF all the three (Self, Internal and External) are present then the Formula should be(20%Self+10%Internal+30%
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