cell functin problem

[mountaindog]

I am using excel 2000 (9.0.2720)

The following is from a simple excel file. It just assigns a letter
grade for a given score. The following works just fine.

=IF(J7>95,"A",IF(AND(J7>90,J7<=95),"A-",IF(AND(J7>85,J7<=90),"B",IF(AND(J7>80,J7<=85),"B-",IF(AND(J7>75,J7<=80),"C",IF(AND(J7>70,J7<=75),"C-",IF(AND(J7>65,J7<=70),"D","F")))))))

The same line indented

=IF(J7>95,"A",
IF(AND(J7>90,J7<=95),"A-",
IF(AND(J7>85,J7<=90),"B",
IF(AND(J7>80,J7<=85),"B-",
IF(AND(J7>75,J7<=80),"C",
IF(AND(J7>70,J7<=75),"C-",
IF(AND(J7>65,J7<=70),"D","F")))))))

above works
---------------
below fails

When I add the last case for the D- condition excel barfs with the
following error:

"The formula you typed contains an error."
=IF(J7>95,"A",IF(AND(J7>90,J7<=95),"A-",IF(AND(J7>85,J7<=90),"B",IF(AND(J7>80,J7<=85),"B-",IF(AND(J7>75,J7<=80),"C",IF(AND(J7>70,J7<=75),"C-",IF(AND(J7>65,J7<=70),"D",if(and(j7>60,j7<=65,"D-","F"))))))))

The same line indented

=IF(J7>95,"A",
IF(AND(J7>90,J7<=95),"A-",
IF(AND(J7>85,J7<=90),"B",
IF(AND(J7>80,J7<=85),"B- ",
IF(AND(J7>75,J7<=80),"C",
IF(AND(J7>70,J7<=75),"C-",
IF(AND(J7>65,J7<=70),"D",
IF(AND(j7>60,j7<=65,"D-","F"))))))))

I have typed this in more times then I can count and I just don't see
the problem. I am sure that it is right in front of me and I am just
not seeing the error. It is as if I have reached a limit on the number
of conditions but, that would be to simple.

I need a second opinion. Can anyone see what the
problem is?

Or,,,,,, am I doing this the hard way. If there is an easier method
please show me.

Thanks
----------

 

[Soo Cheon Jheong]

Hi,


=IF(J7>95,"A",IF(J7>90,"A-",IF(J7>85,"B",IF(J7>80,"B-",
IF(J7>75,"C",IF(J7>70,"C-",IF(J7>65,"D",IF(J7>60,"D-","F"))))))))



--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--

 

[Guest]

"Nesting level limits A formula can contain up to seven
levels of nested functions."

From Excel help....

-----Original Message-----
I am using excel 2000 (9.0.2720)

The following is from a simple excel file. It just assigns a letter
grade for a given score. The following works just fine.

=IF(J7>95,"A",IF(AND(J7>90,J7<=95),"A-",IF(AND

(J7>85,J7<=90),"B",IF(AND(J7>80,J7<=85),"B-",IF(AND

The same line indented

=IF(J7>95,"A",
IF(AND(J7>90,J7<=95),"A-",
IF(AND(J7>85,J7<=90),"B",
IF(AND(J7>80,J7<=85),"B-",
IF(AND(J7>75,J7<=80),"C",
IF(AND(J7>70,J7<=75),"C-",
IF(AND(J7>65,J7<=70),"D","F")))))))

above works
---------------
below fails

When I add the last case for the D- condition excel barfs with the
following error:

"The formula you typed contains an error."
=IF(J7>95,"A",IF(AND(J7>90,J7<=95),"A-",IF(AND

(J7>85,J7<=90),"B",IF(AND(J7>80,J7<=85),"B-",IF(AND

 

[Biff]

Hi!

Build a table like this somewhere on your sheet:

F 0 <60
D- 60 60-65
D 66 65-70
C- 71 70-75
C 76 75-80
B- 81 80-85
B 86 85-90
A- 91 90-95
A 96 >95

Use this formula:

=INDEX(H1:H9,MATCH(J7,I1:I9,1))

H1:H9 = letter grades
I1:I9 = single number grades

Biff

-----Original Message-----
I am using excel 2000 (9.0.2720)

The following is from a simple excel file. It just assigns a letter
grade for a given score. The following works just fine.

=IF(J7>95,"A",IF(AND(J7>90,J7<=95),"A-",IF(AND

(J7>85,J7<=90),"B",IF(AND(J7>80,J7<=85),"B-",IF(AND

The same line indented

=IF(J7>95,"A",
IF(AND(J7>90,J7<=95),"A-",
IF(AND(J7>85,J7<=90),"B",
IF(AND(J7>80,J7<=85),"B-",
IF(AND(J7>75,J7<=80),"C",
IF(AND(J7>70,J7<=75),"C-",
IF(AND(J7>65,J7<=70),"D","F")))))))

above works
---------------
below fails

When I add the last case for the D- condition excel barfs with the
following error:

"The formula you typed contains an error."
=IF(J7>95,"A",IF(AND(J7>90,J7<=95),"A-",IF(AND

(J7>85,J7<=90),"B",IF(AND(J7>80,J7<=85),"B-",IF(AND

 

[Dana DeLouis]

On last line..

IF(AND(j7>60,j7<=65,"D-","F"))))))))

there is no closing ")" for the AND statement. However, this does not solve
the problem. I don't see the real problem either. If I change the last
statement to = IF(TRUE,"D-","F"), then it does not give an error. For a
simple test, if I change the TRUE statement to AND(True,True), then it
throws an error.
Seems like it does not like the last AND statement. If you drop the last
And, it seems to work:
...., IF(J7>60,"D-","F"))))))))

I don't see the problem either at this time.
HTH
Dana DeLouis