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)

<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>
[TD="class: xl74, width: 343, colspan: 5"]Vendor Evaluation
[/TD]
[TD="class: xl66, width: 55"] [/TD]
[TD="class: xl70, width: 63"] [/TD]
[TD="class: xl70, width: 62"] [/TD]
[TD="class: xl70, width: 58"] [/TD]
[TD="class: xl70, width: 72"]
[/TD]
[TD="class: xl70, width: 70"] [/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl68, width: 55, align: right"]1[/TD]
[TD="class: xl68, width: 63, align: right"]2[/TD]
[TD="class: xl68, width: 62, align: right"]3[/TD]
[TD="class: xl68, width: 58, align: right"]4[/TD]
[TD="class: xl68, width: 72, align: right"]5
[/TD]
[TD="class: xl69, width: 70, align: right"]%
[/TD]

[TD="class: xl71, width: 343, colspan: 5"]Timeliness of Deliveries
[/TD]
[TD="class: xl66, width: 55"]
[/TD]
[TD="class: xl66, width: 63"] [/TD]
[TD="class: xl66, width: 62"] [/TD]
[TD="class: xl66, width: 58"] [/TD]
[TD="class: xl66, width: 72"] [/TD]
[TD="class: xl67, width: 70, align: right"]0.00%
[/TD]

[TD="class: xl71, width: 343, colspan: 5"]Quality of Product/Material upon delivery
[/TD]
[TD="class: xl66, width: 55"] [/TD]
[TD="class: xl66, width: 63"] [/TD]
[TD="class: xl66, width: 62"]
[/TD]
[TD="class: xl66, width: 58"] [/TD]
[TD="class: xl66, width: 72"] [/TD]
[TD="class: xl67, width: 70, align: right"]0.00%[/TD]

</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}),"")