Using data ranges in an IF statement

**ell365** · 08-27-2007, 08:07 AM

hey anyone!

can anyone help me with my problem?

I need to be able to give a grade A, B, C or a D by using a range of scores in an IF function for a markbook as part of my IT coursework.

I have had several failed attempts, and this is one of my many formulas that has failed:

=IF(O6<45,"D",IF(O6=$46:$60,"C",IF(O6<$61:$74,"B",IF(O6>75,"A"))))

can anyone tell me what is wrong with it?

PS. I have attached a screenprint.

thankyou.

**NBVC** · 08-27-2007, 08:13 AM

Try:

=LOOKUP(O6,{0,46,61,76},{"D","C","B","A"})

**ell365** · 08-27-2007, 08:21 AM

no, sorry that hasn't worked, it still shows every grade as "D".

I really don't know why, as all the "Overall Percentage Mark" values are different.

Do you know of any other solutions?

**arthurbr** · 08-27-2007, 08:31 AM

Could it be that the formula compares o6(60% which is 0.6 ) to 45 ( and not 0.45) which would explain that you alwas get a "D"?

Try

Please Login or Register  to view this content.

**NBVC** · 08-27-2007, 08:31 AM

It works for me...

Are you sure that your calculations option isn't set to manual?

Check, Tools|Options and go to Calculation tab....the Automatic radio button should be selected.

or what Arthurbr said...

**ell365** · 08-27-2007, 08:39 AM

Thankyou soo much!

that last code worked! My calculations were on automatic by the way.

thankyou again!

ell

**corinereyes** · 08-27-2007, 08:39 AM

Hello,

You are referring to column O with the percentage score on it. Try using column m instead or you can try this formula and paste in P6.

. NBVC function is also working in my worksheet.

Please Login or Register  to view this content.

Hope that helps.

**ell365** · 08-27-2007, 08:49 AM

no sorry corine, i couldnt use that formula because the grade is for the "overall" mark. And column O, is determined by using a formula from the homework mark (column L) and the exam mark (column N), and column M, is only the exam.

but thankyou anyway for your support

ell