Thursday, May 17, 2012

Determine Character from Certain Value in Ms Excel


Title of this post means : if you have a table from a list of values In Ms Excel ​​such as Test Scores, and you want to convert those values ​​to character A, B, C, D or E in other Cells  automatically without have to type it manually one by one, you can use IF Function

For example, the definition :
    Value          Using character   
0-59          E
60-74          D
75-84          C
85-94          B
95-100           A

Then the IF formula should be made in the cells that will be displayed with the Character/ letter :
(On my example below, the value is in cell C2 (column C, row 2), adjust to cell location for yours)

For format using the separator "," (comma / Englih U.S. region format)

= IF (C2 <60, "E", IF (C2 <75, "D", IF (C2 <85, "C", IF (C2 <95, "B", IF (C2 <= 100, "A "," Error ")))))

• For format using separators ";" (semicolon / Malay, Indonesia..  region format)

= IF (C2 <60; "E"; IF (C2 <75; "D"; IF (C2 <85; "C"; IF (C2 <95; "B"; IF (C2 <= 100; "A ";" Error ")))))

Perform copy and paste for the next row / the next line.

Test Scores

Artikel Terkait