I have 3 possible evaluation values in my spreadsheet (I have to use the values with digits and words):

  • 1.0 - Doesn't meet expectations
  • 2.0 - Meets expectations
  • 3.0 - Exceeds expectations

The goal - is to calculate the average evaluation point, like 2.5 I tried to use IF condition, but it works only for 2 values, and I have 3.

Could you please advise how can I calculate in separate column on at the bottom of the column with my values the average digit?

  • 1
    The average of what, exactly? Maybe you could share a spreadsheet with us, demonstrating what you want? – Vidar S. Ramdal Mar 14 '14 at 13:40

You can nest the IF statements, to support any number of options.

Given that cell A1 contains one of 1.0 - Doesn't meet expectations, 2.0 - Meets expectations or 3.0 - Exceeds expectations, input this formula in B1:

=IF(A1="1.0 - Doesn't meet expectations", 1, IF(A1 = "2.0 - Meets expectations", 2, 3))

Another option is to just extract the first three characters of the cell contents, and use that as a number:


The number resulting from any of these formulas can the be used to calculate an average


See the example spreadsheet I set up.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.