iif statement

Microsoft Office access 2007 (upgrade, p...
January 9, 2010 at 06:26:43
Specs: Windows Vista
I am trying to grade the students and i keep getting
"F" with this formula

IIF( [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


See More: iif statement

Report •


#1
January 9, 2010 at 09:13:09
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

http://www.skeptic.com/


Report •

#2
January 9, 2010 at 09:16:28
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	A

If 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


Report •

#3
January 9, 2010 at 09:22:15
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.


Report •

Related Solutions

#4
January 9, 2010 at 09:27:12
Hi DerbyDad03,

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

Regards


Report •

#5
January 9, 2010 at 10:01:39
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

http://www.skeptic.com/


Report •

#6
January 9, 2010 at 10:07:26
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 as

IIF( [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.


Report •


Ask Question