Ran into an interesting question for a client today. I was asked how to take a set of data from a basic Score Key
Score key: #
very positive 1
positive 0.8
neutral 0.5
unrelated 0.5
negative 0.2
very negative 0
And apply that data automatically to a big list of Excel values such as below and have it automatically populate in the correct number.
very positive
positive
very negative
positive
The answer is to use a nested IF function. I built one as follows:
=IF(c15=”very positive”, 1, IF(c15=”positive”, .8, IF(c15=”neutral”, .5, IF(c15=”unrelated”, .5, IF(c15=”negative”, .2, IF(c15=”very negative”, 0, “ERROR”))))))