Help a teacher??

[Tim K.]

Well, her boyfriend anyway.
I'm building a grade sheet for my girlfriend and linking the letter grade
outputs to a report card template in Word and I need to add in something to
this:
=IF(R2>89,"A",IF(R2>79,"B",IF(R2>69,"C",IF(R2>59,"D",IF(R2>0,"F")))))
so that if there are no grades entered it doesn't link the error #div/0
which messes up the report card.
Any ideas would be greatly appreciated
Thanks
T

 

[Max]

Try:

=IF(OR(R2="",R2=0),"",IF(R2>89,"A",IF(R2>79,"B",IF(R2>69,"C",IF(R2>59,"D",IF
(R2>0,"F"))))))

 

[Plum]

-----Original Message-----
Well, her boyfriend anyway.
I'm building a grade sheet for my girlfriend and linking the letter grade
outputs to a report card template in Word and I need to add in something to
this:
=IF(R2>89,"A",IF(R2>79,"B",IF(R2>69,"C",IF(R2>59,"D",IF (R2>0,"F")))))
so that if there are no grades entered it doesn't link the error #div/0
which messes up the report card.
Any ideas would be greatly appreciated
Thanks
T


.

Perhaps;=IF(A1>89,"A",IF(A1>79,"B",IF(A1>69,"C",IF
(A1>59,"D","FAIL"))))

 

[Bob Phillips]

Hi TGim,

An alternative which will allow easier adding of more ranges

=IF(R2="","",VLOOKUP(R2,{0,"F";60,"D";70,"C";80,"B";90,"B";100,"A"},2))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tim K.]

Thanks to all for your ideas but would you believe than not one returns a
blank cell?
It seems so obvious to me that there should be an easy way to return a blank
cell for linking to grade books or any application where you might not have
all the data for the first report, but apparently not.
I suppose I could link to 4 cards for each child so there are no "future"
grade periods but man that seems so convoluted.

 

[Don Guillett]

This should cover all the bases
=IF(LEN(r2)<1,"",LOOKUP(r2,{"",0,60,70,80,90},{"","F","D","C","B","A"}))

 

[Tim K.]

This should cover all the bases
=IF(LEN(r2)<1,"",LOOKUP(r2,{"",0,60,70,80,90},{"","F","D","C","B","A"}))

Still does the same thing. I'm wondering if I'm doing something wrong - or
at least not explaining what I've alreay done well enough so here goes:
I have space for raw scores, then average function, then the conversion to
letter grade, then I had to do another colum where T2=R2 (when I linked R2
to Word it pasted the function instead of the letter grade) and T2 is linked
to the place for the grade in the Word document and without any grades
entered that continues to send #DIV/0! to Word.
Thanks!

 

[Harald Staff]

Still does the same thing. I'm wondering if I'm doing something wrong

Definitely. You complain about something returning #DIV/0. Only a formula
dividing some number by some other number (which now equals 0 or a
nullstring or blank) does that. You wanted a function to return a grade
letter from a number and you got several good ones. There's no way any of
those would return a #DIV/0. So what you want and what you test is something
completely different from what you asked for and got. And what's the

would you believe than not one returns a blank cell?

for ? Of course we believe that.

Best wishes Harald

 

[Tim K.]

Definitely. You complain about something returning #DIV/0. Only a formula
dividing some number by some other number (which now equals 0 or a
nullstring or blank) does that. You wanted a function to return a grade
letter from a number and you got several good ones. There's no way any of
those would return a #DIV/0.

Nope - when I put number grades in I get letter grades. That part works,
and it did before I wrote in. What it won't do is return an empty cell if
nothing's been entered in the number grades. That's what I asked for. I
don't believe, at this point, that Excel is even capable of doing that.
However, I would love to be proved wrong.
I could email you a copy of the sheet of you like.

 

[Frank Kabel]

Nope - when I put number grades in I get letter grades. That part
works, and it did before I wrote in. What it won't do is return an
empty cell if nothing's been entered in the number grades. That's
what I asked for. I don't believe, at this point, that Excel is even
capable of doing that. However, I would love to be proved wrong.
I could email you a copy of the sheet of you like.

Hi Tim
you may post the EXACT formula you have used. As for example Don's
formula should work. What is in your 'empty' cell?. Maybe a space?.

So please post the exact formula used and you may also check what the
formula
=ISBLANK(A1)
returns. Replace A1 with your blank cell reference. It should return
TRUE

Frank

 

[Guest]

I had a similar problem and solved it using ISBLANK but I stopped at excel
level only.
Probably word read what is hidden.
Would like to have your sheet sent to me.

 

[Harald Staff]

Nope - when I put number grades in I get letter grades. That part works,
and it did before I wrote in. What it won't do is return an empty cell if
nothing's been entered in the number grades. That's what I asked for.

So you posted the formula that you're happy with. It would be easier to help
you if you posted the one that you want fixed instead.
The usual way to avoid #DIV/0 errors is like this
=IF(B1=0;"";A1/B1)

HTH. Best wishes Harald

 

[Tim K.]

No I did not post what I was happy with - I posted what I had and what it
didn't do that I needed. Here's the entire sheet, column by column...


Column A is student number.
The next 15 columns are places to enter percentages.
Then a column (R) that calculates the average.
Then a column (T) that contains:
=IF(R2>89,"A",IF(R2>79,"B",IF(R2>69,"C",IF(R2>59,"D",IF(R2>0,"F")))))
to convert to a letter grade, and that is linked to a spot in a word
document report card.
If nothing is entered, regardless of which of the several suggestions I've
received that I use, I get #DIV/0! in the report card and the excel sheet.

Why is this a problem? The report card has 4 grade periods in it and I'm
trying to avoid having to link 4 cards to each student, but it seems that is
the only way to do this. It seems mindbogglingly stupid to me that there be
no way to have the sheet return nothing to the word document if there are no
grades entered, but that seems to be true.
Perhaps there is a way to only activate the sheets as those grade periods
begin?

 

[Tim K.]

I had a similar problem and solved it using ISBLANK but I stopped at excel
level only.
Probably word read what is hidden.
Would like to have your sheet sent to me.

I need a valid email addy - mine is timkozusko at hotmail
dot com.
Thanks

 

[Frank Kabel]

Hi Tim
wha we really need to help you is the formula which results in the
#DIV/0 error. This can't be your IF statement (unless R2 contains the
#DIV/0 error as well).

So please post the formula which returns #DIV/0

If you like email me your file:
frank[dot]kabel[at]freenet[dot]de

 

[Tim K.]

Hi Tim
wha we really need to help you is the formula which results in the
#DIV/0 error. This can't be your IF statement (unless R2 contains the
#DIV/0 error as well).

So please post the formula which returns #DIV/0

It's the average formula.
You should have it by now...
Viele Danks

 

[Frank Kabel]

Hi
file is on the way back to you. For all others:
Problem was an AVERAGE formula:
=AVERAGE(B22)

This returned the #DIV/0 error as all cells B22 were blank. One
solution:
=IF(COUNT(B22),AVERAGE(B22),"")

 

[Tim K.]

Hi
file is on the way back to you. For all others:
Problem was an AVERAGE formula:
=AVERAGE(B22)

This returned the #DIV/0 error as all cells B22 were blank. One
solution:
=IF(COUNT(B22),AVERAGE(B22),"")

It works perfectly now!

Thanks to all and especially to Frank.

 

[Max]

Ah, the understated beauty of barking up the wrong tree ..
is the ng's enrichment with multiple options offered
to skin the poor cat alive, once it's down <bg>

 

[Guest]

Hi Tim

it is hulyalkar at vsnl dot com
I am sending sheet I worked to you. Hope it helps.

Satish