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%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

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

Microsoft Excel

there's no file attached...

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

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