Click here for important information about Computing.net.

Solved Multiple IFs in Excel

November 26, 2020 at 02:55:48
Specs: Windows 8
I'm creating a worksheet where I can get the score of each employee in a risk evaluation.

A2 = Employee Name

B2 = Question ID. The value of the cell can be numbers 92 to 100 or 115 or 116.

C2 = Procedure ID

D2 = Pass, Fail or N/A

E2 = Points lost

I need a formula in E2 for these equations:

1. If D2=Fail and A2=92, 93, 94, 96, 97, 115 or 116 then TRUE (1), FALSE (0)
2. If D2=Fail and A2=95, 98, 99 or 100, then TRUE (10), FALSE (0)

Question IDs 92, 93, 94, 96, 97, 115 and 116 are worth 1 point each.
Question IDs 95, 98, 99 and 100 are worth 10 points each.

F2 = Total Score or (47-E2) / 47

Is this possible?


See More: Multiple IFs in Excel


✔ Best Answer
November 26, 2020 at 08:23:51
Since I do not know your work process, I can only base my answer on your example data.

If you put the following formula in F2 and drag it down, it will produce the 77% for John's Pro1234 but...

1 - It will return 77% in both F2 and F3
2 - If multiple employees have the same Pro ID e.g. Pro1234, then all employees with Pro1234 will have their Loss Points included in their results. It would look something like this if Mark also displayed Pro1234:

Employee	QID	Pro ID	Result	Loss Points	Score
John	        98	Pro1234	Fail	10	        77%
John	        115	Pro1234	Fail	1	        77%
John	        97	Pro1236	Fail	1	        98%
Mark	        98	Pro1234	Pass    0	        77%

If multiple employees having matching Pro ID's can not happen, then you should be good to go. If that can happen, then I'll need to do some more work.

Try this in F2:

=(47-SUMIF($C$2:$C$5,C2,$E$2:$E$5))/47

Note: Do me a favor. If you have any more criteria that need to be met by the solution, please let me know all of them at once. If you keep adding additional criteria like 'I need to sum John's Point Loss values', at some point all previous work may have to be thrown out and I may have to start from the beginning. It's much easier if I only have to work on one overall solution. Thanks.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code



#1
November 26, 2020 at 06:28:43
If A2 contains the Employee Name, how can A2 also contain your numbers?

I assume you mean:

1. If D2=Fail and B2=92, 93, 94, 96, 97, 115 or 116 then TRUE (1), FALSE (0)
2. If D2=Fail and B2=95, 98, 99 or 100, then TRUE (10), FALSE (0)

message edited by DerbyDad03


Reply ↓  Report •

#2
November 26, 2020 at 06:37:09
Yes, my bad. I was pertaining to B2 for the Question IDs.

Reply ↓  Report •

#3
November 26, 2020 at 06:42:23
Try this in E2:

=IF(D2="Fail",IF(OR(B2=92,B2=93,B2=94,B2=96,B2=97,B2=115,B2=116),1,10),0)

This assumes that Data Validation is used for B2 & D2 so that only those specific values can be chosen.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
November 26, 2020 at 07:27:09
Thanks, DerbyDad03. Follow up question: What if I want to combine the lost points if the procedure ID is the same?

Employee	QID	Pro ID	Result	Loss Points	Score
John	        98	Pro1234	Fail	10	        79%
John	        115	Pro1234	Fail	1	        98%
John	        97	Pro1236	Fail	1	        98%
Mark	        98	Pro1237	Pass	0	        100%

For example, John got two fails in QID 98 and 115 for the same procedure. His score for Pro1234 should be (47-11)/47 or 77%. Is there a way that the formula can be enhanced or perhaps another solution so the loss points will be added if the procedure id is the same? The data will be converted to a PivotTable and my goal is to get the score for each employee for each procedure ID on a weekly and monthly basis.


Reply ↓  Report •

#5
November 26, 2020 at 08:23:51
✔ Best Answer
Since I do not know your work process, I can only base my answer on your example data.

If you put the following formula in F2 and drag it down, it will produce the 77% for John's Pro1234 but...

1 - It will return 77% in both F2 and F3
2 - If multiple employees have the same Pro ID e.g. Pro1234, then all employees with Pro1234 will have their Loss Points included in their results. It would look something like this if Mark also displayed Pro1234:

Employee	QID	Pro ID	Result	Loss Points	Score
John	        98	Pro1234	Fail	10	        77%
John	        115	Pro1234	Fail	1	        77%
John	        97	Pro1236	Fail	1	        98%
Mark	        98	Pro1234	Pass    0	        77%

If multiple employees having matching Pro ID's can not happen, then you should be good to go. If that can happen, then I'll need to do some more work.

Try this in F2:

=(47-SUMIF($C$2:$C$5,C2,$E$2:$E$5))/47

Note: Do me a favor. If you have any more criteria that need to be met by the solution, please let me know all of them at once. If you keep adding additional criteria like 'I need to sum John's Point Loss values', at some point all previous work may have to be thrown out and I may have to start from the beginning. It's much easier if I only have to work on one overall solution. Thanks.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#6
November 26, 2020 at 09:15:38
DerbyDad03, you are the best, your formula is exactly what I needed :-)

Reply ↓  Report •

#7
November 26, 2020 at 09:28:20
I'm glad it worked.

If you do need to deal with multiple employees with the same ProID, I think this formula will work. It matches Names and Pro ID's before doing the sum:

=(47-SUMIFS($E$2:$E$5,$C$2:$C$5,C2,$A$2:$A$5,A2))/47

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Ask Question