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

[ExcelBanter AI]

Writing an inequality and using the IF function in Excel

To write an inequality in Excel, use the and < symbols. For example, "20<x<40" can be written as "20<x and x<40" in Excel.

To write "5" in the cell if the inequality is true, use the IF function. Here are the steps:

1. Select the cell where you want to write the result (in this case, "5").
2. Type the following formula in the formula bar:

   =IF(AND(x20,x<40),5,"")

3. Press Enter.

This formula checks if the value of "x" is greater than 20 and less than 40. If the condition is true, it will write "5" in the cell. If the condition is false, it will leave the cell blank.

Note that you need to replace "x" with the cell reference that contains the value of "x". For example, if the value of "x" is in cell A1, you can write the formula as

=IF(AND(A120,A1<40),5,"")

.

=IF(logical_test,value_if_true,value_if_false)

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

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

Hi Margaret

=IF(AND(A120,A1<40),5,0)

where x = A1

Cheers
JulieD

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

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" wrote:

Hi Margaret

=IF(AND(A120,A1<40),5,0)

where x = A1

Cheers
JulieD

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

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+","valu e_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


"Margaret" wrote in message
...
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" wrote:

Hi Margaret

=IF(AND(A120,A1<40),5,0)

where x = A1

Cheers
JulieD

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

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

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

From: BobUmlas ()
Subject: 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/te...100821033.aspx

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Margaret" wrote in message
...
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" wrote:

Hi Margaret

=IF(AND(A120,A1<40),5,0)

where x = A1

Cheers
JulieD

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

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(xOR=20,x<40),5), (IF(AND(xOR=40,x<60),6)),( IF(AND(x=OR60,X<80),7))

Thanks,
Margaret
"Margaret" wrote:

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

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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Margaret" wrote in message
...
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(xOR=20,x<40),5), (IF(AND(xOR=40,x<60),6)),(
IF(AND(x=OR60,X<80),7))

Thanks,
Margaret
"Margaret" wrote:

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

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

or VLOOKUP even :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
<snip

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" wrote:

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+","valu e_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


"Margaret" wrote in message
...
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" wrote:

Hi Margaret

=IF(AND(A120,A1<40),5,0)

where x = A1

Cheers
JulieD

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

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



"Margaret" wrote in message
...
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" wrote:

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+","valu e_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


"Margaret" wrote in message
...
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" wrote:

Hi Margaret

=IF(AND(A120,A1<40),5,0)

where x = A1

Cheers
JulieD

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

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

On Sat, 5 Feb 2005 06:59:02 -0800, "Margaret"
wrote:

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" wrote:

Hi Margaret

=IF(AND(A120,A1<40),5,0)

where x = A1

Cheers
JulieD

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