• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Match with IF formula

Ferena

New Member
Hi,

I am looking for a formula that calculates numbers differently based on the text in column A.

I need to have a summary calculation with multiple criteria for each employee ID. For every employee either External or internal rating will be provided.

If External data is avaialble, then the calculation should be (.7*external+.2*Self+.1*Internal) (ex. formula in column H11) If external data is not available then I would like it to be (.7*self+.3*Internal) (Ex.formula in column C11)

The challenge I am having is there are three rows for each employee ID, so I need to find a formula that combines MATCH and IF function.(or anything other formula that looks up employee IDs and then calculates based on the criteria)
I would like to get the result shown in row 11 and 12.
 

Attachments

  • Book1.xlsx
    9.3 KB · Views: 4
Ferena

Firstly, Welcome to the Chandoo.org Forums

upload_2015-10-2_11-2-50.png

I would first recomend staying with a simple structured layout as you have provided

This allows a simple solution of:
C9: =IF(C4,(0.2*C2)+(0.1*C3)+(0.7*C4),(0.7*C2)+(0.3*C3))
C10: =IF(C7,(0.2*C5)+(0.1*C6)+(0.7*C7),(0.7*C5)+(0.3*C6))

But they are fixed for the rows

If you want a generic solution
C12: =IF(INDEX(C$2:C$7,MATCH($B12&"External",$A$2:$A$7&$B$2:$B$7,0),),(0.2*INDEX(C$2:C$7,MATCH($B12&"Self",$A$2:$A$7&$B$2:$B$7,0),))+(0.1*INDEX(C$2:C$7,MATCH($B12&"internal",$A$2:$A$7&$B$2:$B$7,0),))+(0.7*INDEX(C$2:C$7,MATCH($B12&"External",$A$2:$A$7&$B$2:$B$7,0),)),(0.7*INDEX(C$2:C$7,MATCH($B12&"Self",$A$2:$A$7&$B$2:$B$7,0),))+(0.3*INDEX(C$2:C$7,MATCH($B12&"internal",$A$2:$A$7&$B$2:$B$7,0),))) Ctrl+Shift+Enter

will do the trick

See the attached file:
 

Attachments

  • Farina Book1.xlsx
    12.2 KB · Views: 2
If you want to make it easier to read use Named Formula
Emp_No: =Sheet1!$A$2:$A$7
Type: =Sheet1!$B$2:$B$7

C12: =IF(INDEX(C$2:C$7,MATCH($B12&"External",Emp_No&Type,0),),(0.2*INDEX(C$2:C$7,MATCH($B12&"Self",Emp_No&Type,0),))+(0.1*INDEX(C$2:C$7,MATCH($B12&"internal",Emp_No&Type,0),))+(0.7*INDEX(C$2:C$7,MATCH($B12&"External",Emp_No&Type,0),)),(0.7*INDEX(C$2:C$7,MATCH($B12&"Self",Emp_No&Type,0),))+(0.3*INDEX(C$2:C$7,MATCH($B12&"internal",Emp_No&Type,0),)))

See attached
 

Attachments

  • Farina Book1.xlsx
    12.2 KB · Views: 2
I need to add few more conditions around. Please see the excel spreadsheet attached. Basically, I would like to add more conditions for each scenario and get the result like C32:K38 in the attached file. I have also put the rule against each one.
Thanks again!
 

Attachments

  • Copy of Farina Book1.xlsx
    13 KB · Views: 0
Detailed Descriptions,
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.
 

Attachments

  • Book1.xlsx
    16.1 KB · Views: 0
Back
Top