Vendor Eval Form Formula Question

[tdio]

Hello All,
I have a vendor evaluation form the we use internally and i need a little assistance with a formula or function to use to tally up a score. Currently we have 8 rows each with a specific area to rate and 5 columns with a rating system of 1 - 5 meaning 5 is excellent and 1 is unacceptable. Below is an example of two of the rows (I'm not able to attach a sample)

Vendor Evaluation
					1	2	3	4	5	%
Timeliness of Deliveries						0.00%
Quality of Product/Material upon delivery						0.00%

<colgroup><col style="width:48pt" span="3" width="64"> <col style="mso-width-source:userset;mso-width-alt:2084;width:43pt" width="57"> <col style="mso-width-source:userset;mso-width-alt:3437;width:71pt" width="94"> <col style="mso-width-source:userset;mso-width-alt:2011;width:41pt" width="55"> <col style="mso-width-source:userset;mso-width-alt:2304;width:47pt" width="63"> <col style="mso-width-source:userset;mso-width-alt:2267;width:47pt" width="62"> <col style="mso-width-source:userset;mso-width-alt:2121;width:44pt" width="58"> <col style="mso-width-source:userset;mso-width-alt:2633;width:54pt" width="72"> <col style="mso-width-source:userset;mso-width-alt:2560;width:53pt" width="70"> </colgroup><tbody>
</tbody>

The way the for works currently, is the user needs to place a 1 in one of the 5 columns to calculate the percentage. What we want to do is use an X instead. So for the above Timeliness of deliveries this vendor would get a "3" so i would place a 1 in the 3 column and it would change the 0.00% to 60%. For Quality of Product/Material upon delivery this vendor would get a "5" so i would place a 1 in the column with the 5 heading and the 0.00% column would change to 100%. A field at the bottom of the form calculates the average score. This is the formula of the % column =(F27*20%)+(G27*40%)+(H27*60%)+(I27*80%)+(J27*100%)

To make a long story short, i would like to use an X instead of a 1 to represent a score.

Any input would be greatly appreciated. This is one of those scenarios where each time a look at it i think of another way to do it, yet i can't get it down to work.

Thanks,
TD

 

[gaz_chops]

Try

=LOOKUP(MATCH("X",F27:J27,0),{1,2,3,4,5},{0.2,0.4,0.6,0.8,1})

 

[tdio]

Awesome. It seems to be calculating correctly. Just need to hide the #N/A in the % column when the form is not filled out.

 

[gaz_chops]

You're welcome, just use iferror

=iferror(LOOKUP(MATCH("X",F27:J27,0),{1,2,3,4,5},{0.2,0.4,0.6,0.8,1}),"")