Getting around 'IF"

[Dr Traffic]

I want to calculate a letter grades based on marks. The letter grades
are divided up into a group of 10.

F 0 to 49
D 50 to 54
C- 55 to 59
C 60 to 63
C+ 64 to 67
B- 68 to 71
B 72 to 75
B+ 76 to 79
A- 80 to 84
A 85 to 90
A+ 90 to 100

"IF" only allows 7 functions. How can I get around this restriciton?
I'm somewhat of a novice when it comes to formula's so a bit of an
example would be a great help.

Thanks Rick

 

[Don Guillett]

have a look in the help index for LOOKUP

 

[kassie]

Create a lookup table, I called it Crit, with your symbols in the first
column, and your minimum score per symbol in the 2nd column. Where you want
the result to show, leave a cell blank (I used E4), where you will enter the
student's score. In the next cell enter
=IF(E4="","",VLOOKUP(E4,Crit,2,TRUE)) (I used F4). If different, change E4
to the cell that will contain the student's score.

 

[Gord Dibben]

You could use a VLOOKUP table with two columns.

Column A with the scores and Column B with the grades.

Or this alternative..................

With column of scores in A enter this in B1 and double-click to copy down.

Column B will return the letter grades.

=LOOKUP(A1,{0,50,55,60,64,68,72,76,80,85,91,101},{"F","D","C-","C","C+","B-","B","B+","A-","A","A+"})


Gord Dibben MS Excel MVP

 

[Ragdyer]

You can have a datalist in an "out-of-the-way" location on your WS, or you
can incorporate it within the formula itself.

Say the number grades were in Column D, from D1 down.

Enter this in E1:
=IF(D1,LOOKUP(D1,{0,50,55,60,64,68,72,76,80,85,90;"F","D","C-","C","C+","B-"
,"B","B+","A-","A","A+"}),"")
And copy down as needed.

To use a datalist, enter this in say Y1 to Z11:

Y Z

0 F
50 D
55 C-
60 C
64 C+
68 B-
72 B
76 B+
80 A-
85 A
90 A+

And then enter this formula in E1, and copy down:

=IF(D1,LOOKUP(D1,Y1:Z11),"")

 

[Ragdyer]

Forgot to include absolute references in the last formula to enable copying:

=IF(D1,LOOKUP(D1,$Y$1:$Z$11),"")

 

[ShaneDevenshire]

Hi Ragdyer,

Personally I like VLOOKUP however it can be done with IF by expanding the
idea shown below:

=IF(H1<50,"F","")&IF(AND(H1>49,H1<55),"D","")

You will also be able to do it in 2007 with nested IF's since you will be
allowed 50 levels deep of nesting if my memory servers me.

 

[Don Guillett]

Shane,

Where in the world have you been?

 

[ShaneDevenshire]

Hi Don,

I've been spending all of my free time beta testing VISTA and Office 2007.
That's over so I'll probably be back making the rounds. Nice to hear from
you.

 

[Ragdyer]

Are you saying that you would (prefer to) *USE* 50 levels of an If()
function?

 

[Don Guillett]

And your recommendation is____________?

 

[ShaneDevenshire]

Hi Don,

I presume you are asking me whether I prefer Office 2007 to 2003 and VISTA
to Windows XP?

Office 2007: upside - the biggest feature upgrade since version 5 or maybe
ever. downside - complete revision of the GUI requiring extensive relearning
and loose of some very nice features. Since we are probably all going there
eventually, maybe the sooner the better. (MAC version is loosing VBA and
there will be no code converter!)

VISTA is a little harder to evaluate. For myself I would have prefered more
changes, many of the planned items in the original feature set were not
included which is a little disappointing. Security enhancements will be
paramont for the enterprise, and eventually we'll all be on it.

In both cases older machines may not be able to handle the upgrades so new
hardware is going to be a requirement. For example, OFFICE 2007 on my 1GH
Dell is impossible, but buzzes along on my Core due 2.3 GH laptop.

I only put VISTA on a 2 year old 3GHZ machine and it worked fine.

Cheers,
Shane

 

[ShaneDevenshire]

Hi,

If you read my original message you will see that I prefer VLOOKUP, but I
just want to inform users of features in the upcoming version. Personally,
when I see seven levels of nesting I almost always assume there could have
been a better way. But Excel users have been complaining for years about the
7 level limit since DOS versions of Lotus 1-2-3 and Quartro Pro could do more.

Happy new year to all!

 

[T. Valko]

The new nesting level is 64.

I've heard that Vista's main "objective" is DRM compliance. Hence, the
hardware problems.

http://www.cs.auckland.ac.nz/~pgut001/pubs/vista_cost.txt

Biff

 

[Harlan Grove]

ShaneDevenshire wrote...
....

Office 2007: . . . (MAC version is loosing VBA and
there will be no code converter!)

So no backwards compatibility with any workbooks implementing UDFs in
VBA? Gee, will XLM still be supported? Is Microsoft going to provide
any simple tool for creating UDFs? If not, aren't they ceding the Mac
market to OpenOffice and the other remaining competitors?

VISTA is a little harder to evaluate. For myself I would have prefered more
changes, many of the planned items in the original feature set were not
included which is a little disappointing. Security enhancements will be
paramont for the enterprise, and eventually we'll all be on it.

....

If security were truly paramount, everyone would be using Macs, Linux
or BSD boxes already. And all e-mail servers would strip out any tags,
scripts, images, attached files, etc. That this hasn't happened yet
undermines the case that security is valued more than perceived
functionality.

 

[Harlan Grove]

ShaneDevenshire wrote...
....

. . . But Excel users have been complaining for years about the
7 level limit since DOS versions of Lotus 1-2-3 and Quartro Pro could do more.

Pretty much every other spreadsheet ever sold or otherwise distributed
(possibly excepting Microsoft's own, failed Multiplan) could handle
more. All versions of 123 and Quattro Pro from earliest DOS versions to
current Windows versions, OpenOffice, and shareware spreadsheets. Even
the now ghostware Sphygmic Spreadsheet, weighing in at a whole 161KB,
can handle more. Excel was always MILES behind EVERYONE else in this
regard. It was one of the stupidest design decisions Microsoft ever
made and pig-headedly stuck with for so long. That they've finally
lifted this should be welcome, but only in the same sense that one
would welcome the recognition and cessation of any willfully stupid
behavior. Certainly not meriting any other than ironic congratulations.

 

[Don Guillett]

Shane,
The reason I ask is that I have been putting off buying a new computer until
the 2007 os & office were available. I'm now wondering if this is good
thinking. Perhaps I should buy with the "upgrade" to 2007 available at no
additional cost.