How do I write inequality?

[Guest]

How do I write an inequality in excel? For example, if 20<x<40 then write 5
in the

 

[Ken Wright]

=IF(logical_test,value_if_true,value_if_false)

=IF(AND(x>20,x<40),5,"Something else")

 

[JulieD]

Hi Margaret

=IF(AND(A1>20,A1<40),5,0)

where x = A1

Cheers
JulieD

 

[Guest]

Thank you, Julie.
Can I write a series of inequalities? I am a teacher and I want the grade
to be posted in the column. So if my grade boundaries are the followin:
95-100 is an A+, 90-95 is an A... and so on for the rest of my grades
Thanks,
Margaret

 

[JulieD]

Hi Margaret

yes you can (in fact this is the example i give my students), however you
can only nest 7 IF functions
e.g.
=IF(A1>=95,"A+",IF(A1>=90,"A",IF(A1>=80,"B+","value_if_false")))
is already 2 nested ifs

so an alternative is to use the VLOOKUP function, to do this you need to
list your grade boundaries in ascending order and the associated grade next
to them
e.g. on Sheet2
........A.....................B
1....Grade...........Result
2......0....................F
3.....50...................P
4.....60...................D
5.....70...................C
etc
then when you're determining the result use the following formula
=VLOOKUP(A1,Sheet2!$A$2:$B$10,2)

this says look up the value in A1 (ie the student's score) in column A of
sheet2 and return the matching result in the second column of this table.

Hope this helps
Cheers
JulieD

 

[Ken Wright]

Option 1
======
http://cpearson.com/excel/excelF.htm#Grades

Option 2
======
Have a look at this thread. via Bob Umlas.

From: BobUmlas ()
Subject: Re: Vlookup also
View: Complete Thread (3 articles)
Original Format
Newsgroups: microsoft.public.excel.worksheetfunctions
Date: 1999/07/12


Set up the table like this (5 rows, 2 columns)
0 F
60 D
70 C
80 B
90 A
Name these 5r x 2c "Grades" (Not required, but good practice).
To find someone's grade using the value from column K, enter
=VLOOKUP(K2,Grades,2)
Where K2 is one of the grades. The 2 in the formula says to return the value
from the 2nd column of the table called "Grades".

Option 3
======
Microsoft Education resources
http://www.microsoft.com/education/Default.aspx

Various educational planning templates
http://office.microsoft.com/en-us/templates/CT062100821033.aspx

 

[Guest]

Thanks for your help, Ken. I am not sure how to write greater than or equal
to and am not sure what to do if I have multiple inequalities.
If I have multiple inequalities for a row would I write the following:

=IF(AND(x>OR=20,x<40),5), (IF(AND(x>OR=40,x<60),6)),( IF(AND(x>=OR60,X<80),7))

Thanks,
Margaret

 

[Ken Wright]

See either mine or Julie's last posts for a VLLOKUP solution. Lots of free
templates to be had on the MS site as well.

 

[Ken Wright]

See either mine or Julie's last posts for a VLLOKUP solution.

or VLOOKUP even

 

[Guest]

Hi Julie,
When I typed in the function, I got the message #N/A.
What does the B$10,2 mean?
Should the number appear in the column that I am typing the function in?
Does it matter how many grade boundaries I type in?(+ and - for each grade)
Thanks,
Margaret

 

[JulieD]

Hi Margaret

What does the B$10,2 mean?

=VLOOKUP(A1,Sheet2!$A$2:$B$10,2)
this says that the table you're looking for the grade in to return the
result is on sheet2 starting at A2 and going to B10
so you need to adjust this to the sheet name & range of your actual data.
the 2 tells Excel that you want to find the value that you've typed in A1 in
sheet2! column A and return the associated bit of information from column B
(ie the 2nd column of the table)

the A1 is where the student's score is

Should the number appear in the column that I am typing the function in?

- not sure what you mean by this

Does it matter how many grade boundaries I type in?(+ and - for each
grade)

no as long as they are single numbers and in ascending order (as per my
example)

if this doesn't help, please post back with the sheet name and references of
your "lookup" table
and the cell reference of the first student's result that you want to return
the grade for.

Cheers
JulieD

 

[Gord Dibben]

Margaret

=LOOKUP(A1,{0,40,50,60,70,80,90,100},{"E","D","C-","C","C+","B","B+","A"})

Adjust ranges and letter scores as required.


Gord Dibben Excel MVP