Help required to prepare a score board with the right option

[G.Madhavi]

Hello All: I am trying to prepare a score sheet with the below details:

I have certain requirements that will be assessed against 3 parameters : Frequency, Effectiveness and Implications. These 3 are entered in 3 columns in excel and the user can select from the 3 drop down options for these 3 parameters, the scores for each option are :

Frequency Effectiveness Criticality Values to be assigned

Never Not effective Non-critical 0
Sometimes Somewhat effective Moderately critical 1
Always Effective Critical 3

Now, I need to calculate the score in another column for each row which has individual requirements against the above 3 factors. Once the user selects x frequency, y effectiveness and z criticality, the score in the H column should show cumulative figure. This is the problem. Please tell me whether Vlookup or which function will be used to best resolve this. Thank you.

Regards
Madhavi

[protonLeah]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[AliGW]

Somewhere in your workbook set up a lookup array with your descriptors and values thus:

Excel 2016 (Windows) 32 bit

	C	D
2	Never	0
3	Not effective	0
4	Non-critical	0
5	Sometimes	1
6	Cannot say	1
7	Moderately critical	1
8	Always	3
9	Effective	3
10	Critical	3

Sheet: Sheet2

Then you will be able to look up values like this:

Excel 2016 (Windows) 32 bit

	C	D
13	Sometimes	1

Sheet: Sheet2

Excel 2016 (Windows) 32 bit

	C	D
13	Sometimes	=VLOOKUP(C13,$C$2:$D$10,2,0)

Sheet: Sheet2

It's then just a matter of having 3 VLOOKUPS in the one cell adding together, e.g.

=IFERROR(VLOOKUP(C13,$C$2:$D$10,2,0)+VLOOKUP(D13,$C$2:$D$10,2,0)+VLOOKUP(E13,$C$2:$D$10,2,0),"")

where c13, D13 and E13 contain the descriptors chosen from the drop-downs. The addition of the IFERROR function leaves the cell blank until there is something to sum.

[G.Madhavi]

Hi, The solution by Ali is working fine but a small issue: When the user selects option Effective, the score becomes blank. Not sure what the issue is. I tried changing the font etc., but did not work out. Other options and values retrieved are fine.. please suggest.

[oeldere]

Effective 3

The value in the VLookup table is empty?

[G.Madhavi]

=IFERROR(VLOOKUP(F7,$C$80:$D$88,2,0)+VLOOKUP(G7,$C$80:$D$88,2,0)+VLOOKUP(H7,$C$80:$D$88,2,0),"")

This is the function i have used. I am able to get the respective values when i select other parameters such as not effective, always, moderately critical etc., but the moment i select Effective from the options, the resultant score field becomes blank.

[oeldere]

Effective 3

1) The value in the VLookup table is empty?

2) the name effective is not available in the VLookup table?

[G.Madhavi]

Hi, The value in the look up table is 3 not empty (i checked once again)
The name is also available.. Below is the table : Dont get what could be the issue..

Never 0
Not effective 0
Non-critical 0
Sometimes 1
Cannot say 1
Moderately critical 1
Always 3
Effective 3
Critical 3

[oeldere]

In that case shows us the file with the failure in it. Please also add the cell of the failure (we don't want to look through the whole file, where the formula should be).

[G.Madhavi]

Attached the file.. Now the same issue with Critical option as well.. when the user selects critical or effective, the score becomes blank. Otherwise, the respective value is retrieved.. please check.Score sheet1.xlsx

[oeldere]

You do not use the datavalidation on the selections.

In cel F6 is a space after the word Effective . So it will not find it in the VLookuptable.

Remove the space and you will see it is working.

For that reason it is usefull to work with datavalidation (you can't get wrong input).

Edit:

The datavalidation Effectiveness refers to wrong cells (failure).

[oeldere]

I would appreciate if you reply on the solution in #11.

[G.Madhavi]

Hi Ali, Oeldere : Thank you very much. The solution is working fine now. Thanks again.

[oeldere]

You're welcome. We appreciate the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.