i'm trying to put a value of a number to print out a letter such as 93 to
100=A, and so on here is what I
have,=IF(B28=93,"a","B"),if(then(B28<=85,"c"),if( then(b28<=70,"D"),if(then(b28=69,"f"))), what do i have wrong.

=if(B28<60,"F",if(B28<70,"D",if(B28<85,"C",If(B28< 93,"B","A"))))

< 60 F
60 - <70 D
70 - <85 C
85 < 93 B
else A

adjust to fit your actual criteria.

--
Regards,
Tom Ogilvy



"dave" wrote in message
...
i'm trying to put a value of a number to print out a letter such as 93 to
100=A, and so on here is what I

have,=IF(B28=93,"a","B"),if(then(B28<=85,"c"),if( then(b28<=70,"D"),if(then(
b28=69,"f"))), what do i have wrong.

Hi,
Try the follwing instead:
- Assuming the follwoing grading system:
- 93-100: A
- 85-93: B
- 70-85: C
- 69-70: D
- below 69: F
- In some range, say Sheet2 starting in row 2, enter the grade numbers in
column A and the matching letters in column B starting with the F grade.
SOmething like:
0 F
69 D
70 C
85 B
93 A
THis should be in the range A2:B6
- Now for your IF formula, replace it by:

= VLOOKUP( B28 , Sheet2!$A$2:$B$6 , 2 , TRUE )

This way, when you change your grading system, you just need to change the
table in Sheet2
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"dave" wrote:

i'm trying to put a value of a number to print out a letter such as 93 to
100=A, and so on here is what I
have,=IF(B28=93,"a","B"),if(then(B28<=85,"c"),if( then(b28<=70,"D"),if(then(b28=69,"f"))), what do i have wrong.

"dave" wrote in message
...
i'm trying to put a value of a number to print out a letter such as 93 to
100=A, and so on here is what I
have,=IF(B28=93,"a","B"),if(then(B28<=85,"c"),if( then(b28<=70,"D"),if(then(b28=69,"f"))),
what do i have wrong.

If we just stick to the first two tests this is why you messed everything
up:

If (b28 =93 , a ,b)

means that is the value in B28 is greater than or equal to 93 the value in
this cell will be "a" and in all other cases "b".

however if you write:
=if(b28=93,a,if(b28=100,b,c))

this would meanther
if the value in B28 is greater than or equal to 93 this value would be a and
in all other cases if the value in B28 would be greater than or equal to
100 it would be b.

This has allready been checked be your first test, however. So any value
greater than or 93 would result in a.

If you wanto solve the problem by using nested IF functions you need to be
careful of the build-up of the logical tests.

So

=if(b2870;"f";if(b28=69;"e";if(b28=,68,"d" ... and so forth

which means
if the value in B28 is greater than 70 this value should be f and else if
the value in b28 is greater than or equal to 69 this value should be e and
else if the the value in B28 shoud be greater than equal to 68 this value
should be d ......

Problem with the IF function however is, that you only have 8 possibilities
(you can nest IF only 7 times).

That is why Sebastien's solution is so much more elegant and easier to read.
Also, using the Vlookup function makes it lots easier to update to new
values.

Just update the table, and you're done.

Would you use nested IF-functions you would have to rewrite your function
all over again.


Good luck,

Sybolt