Microsoft Office access 2007 (upgrade, p...

I am trying to grade the students and i keep getting

"F" with this formulaIIF( [Grade (%)] BETWEEN 0 AND 35,'F',IIF( [Grade (%)] BETWEEN 36 AND 55, 'D',IIF( [Grade (%)] BETWEEN 56 AND 70,'C', IIF( [Grade (%)] BETWEEN 71 AND 90,'B','A'))))

Please help

It has been a very long time since I did anything with Access and you might want to ask your question in the Programming or Database forums. But you might try this and see if it actually works:

IIF( [Grade (%)] <= 35,'F',IIF( [Grade (%)] <= 55, 'D',IIF( [Grade (%)] <= 70,'C', IIF( [Grade (%)] <=90,'B','A'))))

MIKE

Hi, If you are doing this in Access, your problem may be that you are testing an underlying value 0 to 1, rather than 0 to 100. Try changing your values to 0, 0.35, 0.36 etc.

In Excel you could do this:

If you are trying to get a Grade for a value, you can do this with the VLOOKUP() function.Enter this into cells D1 to E5:

D E 1 0 F 2 36 D 3 56 C 4 71 B 5 90 AIf there is a score as a number (0 to 100) in cell A1, put this formula in cell B1:

=VLOOKUP(B1,$D$1:$E$5,2)If the value in B1 is a percentage, i.e., a value from 0 to 1 formatted as a percentage, change the values in column D to

0, 0.36 etc, then format them as percentages.Regards

Humar, I believe the OP is using Access, not Excel, but you went to same place I was going.

I don't do Access, but could it be an "order of magnitude" problem?

.26 if between 0 and 35, therefore a similar Excel IF statement would always find the first condition to be TRUE and return an "F".

Maybe vinodk1 should be using:

[Grade (%)] BETWEEN 0 AND .35

Note the addition of the decimal point.

Hi DerbyDad03, I realized my error from Mike's reply, so changed my response.

Regards

DerbyDad03:

Never thought of the"order of magnitude"So the formula should be something like:

IIF([Grade (%)] <= .35,'F',IIF([Grade (%)] <= .55, 'D',IIF([Grade

(%)] <= .70,'C', IIF([Grade (%)] <=.90,'B','A'))))MIKE

Hi,

Thanks for getting back to me. I found out the table was holding the values in 0.00 format as mentioned above, I was able to modify the query and run it successfully. I changed it to the following asIIF( [Grade (%)] BETWEEN 0 AND 0.55,'F',

IIF( [Grade (%)] BETWEEN 0.56 AND 0.6299, 'D-',

IIF( [Grade (%)] BETWEEN 0.63 AND 0.6599, 'D',

IIF( [Grade (%)] BETWEEN 0.66 AND 0.6899, 'D+',

IIF( [Grade (%)] BETWEEN 0.69 AND 0.7299, 'C-',

IIF( [Grade (%)] BETWEEN 0.73 AND 0.7599, 'C',

IIF( [Grade (%)] BETWEEN 0.76 AND 0.7899, 'C+',

IIF( [Grade (%)] BETWEEN 0.79 AND 0.8299,'B-',

IIF( [Grade (%)] BETWEEN 0.83 AND 0.8599, 'B',

IIF( [Grade (%)] BETWEEN 0.86 AND 0.8899, 'B+',

IIF( [Grade (%)] BETWEEN 0.89 AND 0.9299, 'A-',

IIF( [Grade (%)] BETWEEN 0.93 AND 0.9599, 'A',

IIF( [Grade (%)] BETWEEN 0.96 AND 0.9899,'A+','A++')))))))))))))Thanks for replying back. Appreciate all your help.

Ask Your Question

Weekly Poll

Do you think Samsung's Bixby will compete well against other phone AI systems?

Discuss in The Lounge

Poll History