How do I write inequality?

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

=IF(logical_test,value_if_true,value_if_false)

=IF(AND(x>20,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" <Margaret@discussions.microsoft.com> wrote in message
news:B325C744-89AE-4929-AA2A-8CCDEBDF9D3C@microsoft.com...
> How do I write an inequality in excel? For example, if 20<x<40 then write
5
> in the

Hi Margaret

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

where x = A1

Cheers
JulieD

"Margaret" <Margaret@discussions.microsoft.com> wrote in message
news:B325C744-89AE-4929-AA2A-8CCDEBDF9D3C@microsoft.com...
> 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(A1>20,A1<40),5,0)
>
> where x = A1
>
> Cheers
> JulieD
>
> "Margaret" <Margaret@discussions.microsoft.com> wrote in message
> news:B325C744-89AE-4929-AA2A-8CCDEBDF9D3C@microsoft.com...
> > 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+","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


"Margaret" <Margaret@discussions.microsoft.com> wrote in message
news:2D0E8AF6-E408-4B59-9555-2C96E76D3125@microsoft.com...
> 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(A1>20,A1<40),5,0)
>>
>> where x = A1
>>
>> Cheers
>> JulieD
>>
>> "Margaret" <Margaret@discussions.microsoft.com> wrote in message
>> news:B325C744-89AE-4929-AA2A-8CCDEBDF9D3C@microsoft.com...
>> > How do I write an inequality in excel? For example, if 20<x<40 then
>> > write
>> > 5
>> > in the
>>
>>
>>

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+","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
>
>
> "Margaret" <Margaret@discussions.microsoft.com> wrote in message
> news:2D0E8AF6-E408-4B59-9555-2C96E76D3125@microsoft.com...
> > 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(A1>20,A1<40),5,0)
> >>
> >> where x = A1
> >>
> >> Cheers
> >> JulieD
> >>
> >> "Margaret" <Margaret@discussions.microsoft.com> wrote in message
> >> news:B325C744-89AE-4929-AA2A-8CCDEBDF9D3C@microsoft.com...
> >> > 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" <Margaret@discussions.microsoft.com> wrote in message
news:69D2DD4D-E4A2-42A9-8218-6DB0D235EC4F@microsoft.com...
> 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+","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
>>
>>
>> "Margaret" <Margaret@discussions.microsoft.com> wrote in message
>> news:2D0E8AF6-E408-4B59-9555-2C96E76D3125@microsoft.com...
>> > 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(A1>20,A1<40),5,0)
>> >>
>> >> where x = A1
>> >>
>> >> Cheers
>> >> JulieD
>> >>
>> >> "Margaret" <Margaret@discussions.microsoft.com> wrote in message
>> >> news:B325C744-89AE-4929-AA2A-8CCDEBDF9D3C@microsoft.com...
>> >> > 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: 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/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" <Margaret@discussions.microsoft.com> wrote in message
news:2D0E8AF6-E408-4B59-9555-2C96E76D3125@microsoft.com...
> 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(A1>20,A1<40),5,0)
> >
> > where x = A1
> >
> > Cheers
> > JulieD
> >
> > "Margaret" <Margaret@discussions.microsoft.com> wrote in message
> > news:B325C744-89AE-4929-AA2A-8CCDEBDF9D3C@microsoft.com...
> > > 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"
<Margaret@discussions.microsoft.com> 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(A1>20,A1<40),5,0)
>>
>> where x = A1
>>
>> Cheers
>> JulieD
>>
>> "Margaret" <Margaret@discussions.microsoft.com> wrote in message
>> news:B325C744-89AE-4929-AA2A-8CCDEBDF9D3C@microsoft.com...
>> > 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(x>OR=20,x<40),5), (IF(AND(x>OR=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" <Margaret@discussions.microsoft.com> wrote in message
news:807A06F8-03AE-4969-81F1-0CC6315DFE49@microsoft.com...
> 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
> "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>