Excel Nesting IF Function

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”))))))

Published by

Joel Gross

Joel Gross is the CEO of Coalition Technologies.