=MATCH(B1,{"Non-compliance","Partial Compliance","Full Compliance"},0)-1

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"CT1974" wrote in message
...
I hope you can help.

I am currently designing a spreadsheet which will help us assess our
compliance with certain NHS standards.

Each standard has a cell. Each cell to the right of the standard has a
pull
down list, where users can select 'Full Compliance', 'Partial Compliance'
or
'Non-compliance' with regards to how well we are doing against each
standard.

I would like each of these text responses to have a numerical value (e.g.
Full Compliance =2, Partial Compliance=1, Non-compliance=0) and I would
like
this numerical value to automatically appear in the cell to the right of
the
text response. Is this possible? I have tried the SUBSTITUTE function, but
this will only allow me to give one of the text responses a numberical
value.
When I try and add details for the other two, errors occur.

Am quite new to using Excel, so any help would be much appreciated. Thanks