MS Excel - is this possible?

FreddieFrugal

Hello there,

I was wondering if anyone knew whether it was possible to use a formula that would tell a cell to display a particular symbol or word if a specific number value was put in it.

For instance if I'm making a spreadsheet to input test results. I could have Paper A, Paper B, Paper C and Total columns. Then from the total score column the spreadsheet could work out what level that equalled. E.g. any value between 19-25 would be a 3C. So It would automatically display that in a cell.

Has anyone tried doing something similar before? I've researched it in the past and haven't found anything. It's hard to search for something that specific.

Humphrey10

Read this:
http://spreadsheets.about.com/od/tipsandfaqs/qt/if_enter_text.htm
I think that's what you want.

esuhl

It looks like you'll need to nest an AND in that IF. I haven't used Excel for years, but I think this should do it.

Let's say you're on row 1. Paper A mark is in column A, etc. The total is in D1. So in cell E1 (the level) you could put:


=IF(AND(D1>=19,D1<=25),"3C",<else>)

The bit that says <else> would then to contain an IF statement for the next range (say, 26 to 30 marks), then in the <else> bit at the end of the 2nd IF statement, you could put a third IF, and so on until you have all ranges of marks covered.

Hope that makes sense!

SalsaDanca

The nested IF statements will work, but editing the formula might get confusing if you need more than a few possible grades, so here's another way to do it:

If the first total score is in cell D1, and you want to put a grade in cell E1, you can use the VLOOKUP function. First, set up a table of scores and grades in columns G and H. The grades go in column H, and the lowest score that will awarded that grade goes in column G. Column G must be in ascending order, so the two columns will look a bit like this:

0 Fail
19 3C
26 3B
35 3A
100 Smarty Pants
101 Impossible Score

Now, just enter =VLOOKUP(D1,$G$1:$H$6,2,TRUE) in cell E1. The $G$1:$H$6 part tells Excel where to look for the table of scores and grades, so if you have more or less that six rows in your table you will have to change the 6 in the formula. If you aren't using columns G or H of the spreadsheet for anything except that table, you could use the formula =VLOOKUP(D1,$G:$H,2,TRUE) instead.

Mobeer

Follow SalsaDanca's advice, but but the lookup table into a separate sheet to keep it clearly separate from the marks and grades.