Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
alexander_geoff
 
Posts: n/a
Default Greater than, smaller than formula


When grading children's test scores I want to apply letters and numerals
to particular ranges eg between 21 and 25=3c 26and 30=3b. Please help
with a formula.


--
alexander_geoff
------------------------------------------------------------------------
alexander_geoff's Profile: http://www.excelforum.com/member.php...o&userid=29822
View this thread: http://www.excelforum.com/showthread...hreadid=495258

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
BenjieLop
 
Posts: n/a
Default Greater than, smaller than formula


alexander_geoff Wrote:
When grading children's test scores I want to apply letters and numerals
to particular ranges eg between 21 and 25=3c 26and 30=3b. Please help
with a formula.


You can do this ...

In Columns A and B, you can enter the following:

Cell A1 : 21 Cell B1: 3a
Cell A2 : 26 Cell B2: 3b
Cell A3: 31 Cell B3: 3c
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove
 
Posts: n/a
Default Greater than, smaller than formula

alexander_geoff wrote...
When grading children's test scores I want to apply letters and numerals
to particular ranges eg between 21 and 25=3c 26and 30=3b. Please help
with a formula.


Use the LOOKUP or VLOOKUP formula and a table, perhaps the following in
A1:B10

0 7
6 6
11 5
16 4
21 3c
26 3b
31 3a
36 2
41 1
46 0

Then with a score in cell X99, the grade would be

=LOOKUP(X99,$A$1:$B$10)

or

=VLOOKUP(X99,$A$1:$B$10,2)

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
alexander_geoff
 
Posts: n/a
Default Greater than, smaller than formula


BenjieLop Wrote:
You can do this ...

In Columns A and B, you can enter the following:

Cell A1 : 21 Cell B1: 3a
Cell A2 : 26 Cell B2: 3b
Cell A3: 31 Cell B3: 3c
.
.
.
Cell A100: 95 Cell B100: 3z

NOTE: In Column A, the lower limit of the test score is what you will
enter.

And, in (say, Cell D1), you can enter this formula

=VLOOKUP(C1,$A$1:$B$100,2)

where Cell C1 will contain the child's test score.

Hope this will help you.

Regards.




Let's assume the children's names are in A1:A30. Their scores are in
B1:B30. Please give me precise instructions from here on.
Thanks in anticipation.


--
alexander_geoff
------------------------------------------------------------------------
alexander_geoff's Profile: http://www.excelforum.com/member.php...o&userid=29822
View this thread: http://www.excelforum.com/showthread...hreadid=495258

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gord Dibben
 
Posts: n/a
Default Greater than, smaller than formula

Alexander

=LOOKUP(A1,{0,21,26,31,36},{"fail","3c","3b","3a", "star"})

Adjust to suit more ranges if desired.


Gord Dibben Excel MVP


On Wed, 21 Dec 2005 11:50:49 -0600, alexander_geoff
<alexander_geoff.20eitn_1135187737.0985@excelfor um-nospam.com wrote:


When grading children's test scores I want to apply letters and numerals
to particular ranges eg between 21 and 25=3c 26and 30=3b. Please help
with a formula.



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
BenjieLop
 
Posts: n/a
Default Greater than, smaller than formula


alexander_geoff Wrote:
Let's assume the children's names are in A1:A30. Their scores are in
B1:B30. Please give me precise instructions from here on.
Thanks in anticipation.


With the children's names in A1:A30 and their corresponding test scores
in B1:B30,a no-frills solution is as follows:

1. *Construct a table * (say, use Columns G and H) and enter the
following:

Cell G1=21 and Cell H1=3c
Cell G2=26 and Cell H2=3b
Cell G3=31 and Cell H3=3a

2. In Cell C1, enter the following formula

=VLOOKUP(B1,$G$1:$H$3,2)

3. Copy this formula down until C30. To copy a formula all the way down
to C30, do the following:

*** point your mouse to the right bottom corner of Cell C1
*** as soon as you see a "black crosshair" (*+*), left click on the
mouse and drag it down to Cell C30
*** release the mouse as soon as you are in Cell C30

Hope this is what you are looking for.


Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=495258

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
alexander_geoff
 
Posts: n/a
Default Greater than, smaller than formula


Worked thanks
BenjieLop Wrote:
With the children's names in A1:A30 and their corresponding test scores
in B1:B30,a no-frills solution is as follows:

1. *Construct a table * (say, use Columns G and H) and enter the
following:

Cell G1=21 and Cell H1=3c
Cell G2=26 and Cell H2=3b
Cell G3=31 and Cell H3=3a

2. In Cell C1, enter the following formula

=VLOOKUP(B1,$G$1:$H$3,2)

3. Copy this formula down until C30. To copy a formula all the way down
to C30, do the following:

*** point your mouse to the right bottom corner of Cell C1
*** as soon as you see a "black crosshair" (*+*), left click on the
mouse and drag it down to Cell C30
*** release the mouse as soon as you are in Cell C30

Hope this is what you are looking for.


Regards.



--
alexander_geoff
------------------------------------------------------------------------
alexander_geoff's Profile: http://www.excelforum.com/member.php...o&userid=29822
View this thread: http://www.excelforum.com/showthread...hreadid=495258

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
BenjieLop
 
Posts: n/a
Default Greater than, smaller than formula


You are welcome. I am glad that I can be of help to you.

Regards and Merry Christmas to you and yours.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=495258

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
nekteo
 
Posts: n/a
Default Greater than, smaller than formula

i hv a question...

Is there any formula in excel that can convert a certain number to a a number
that is greater than the previous number, which is an integer number? for
example:


23.56 convert to 24
24.00 convert to 25
23.12 convert to 24
19.00 convert to 20
42.10 convert to 43

HELP ME!!!
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dave Peterson
 
Posts: n/a
Default Greater than, smaller than formula

It looks like you could just add one and take the integer portion:

=int(a1+1)

If A1 held the original number.

nekteo wrote:

i hv a question...

Is there any formula in excel that can convert a certain number to a a number
that is greater than the previous number, which is an integer number? for
example:

23.56 convert to 24
24.00 convert to 25
23.12 convert to 24
19.00 convert to 20
42.10 convert to 43

HELP ME!!!


--

Dave Peterson


  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
nekteo
 
Posts: n/a
Default Greater than, smaller than formula

thank you very much!!
But can u teach me one more thing? I'll really appreciate it.

Example:

I hv three column, Named A1, A2, and, A3.
How can i ask excel to add "false" in A3 if A2 is greater than A1?
And add "True" to A3, if A2 is less than A2
  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dave Peterson
 
Posts: n/a
Default Greater than, smaller than formula

in A3:

=a2<a3

(you've got a typo in your explanation ("if A2 is less than A2").

Or
=a2a3
depending on what you really meant.

Or even
=a2=A3
or
=a2<=a3

(what happens when they're equal?)

nekteo wrote:

thank you very much!!
But can u teach me one more thing? I'll really appreciate it.

Example:

I hv three column, Named A1, A2, and, A3.
How can i ask excel to add "false" in A3 if A2 is greater than A1?
And add "True" to A3, if A2 is less than A2


--

Dave Peterson
  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dave Peterson
 
Posts: n/a
Default Greater than, smaller than formula

I had typos in my response--ignore that earlier version:

in A3:

=a2<a1

(you've got a typo in your explanation ("if A2 is less than A2").

Or
=a2a1
depending on what you really meant.

Or even
=a2=A1
or
=a2<=a1

(what happens when they're equal?)

(I changed all the A3's to A1's in the formulas)

nekteo wrote:

thank you very much!!
But can u teach me one more thing? I'll really appreciate it.

Example:

I hv three column, Named A1, A2, and, A3.
How can i ask excel to add "false" in A3 if A2 is greater than A1?
And add "True" to A3, if A2 is less than A2


--

Dave Peterson
  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
nekteo
 
Posts: n/a
Default Greater than, smaller than formula

thanks... I don know wat to do without your help! I need to pass this
assignment next week!
  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
nekteo via OfficeKB.com
 
Posts: n/a
Default Greater than, smaller than formula

one more question...
You hv thought me how to make a value greater, but wat is the formula to make
it smaller??
For example,
90 convert to 89
98.2 convert to 98
98.8 convert to 98
92.4 convert to 92

I tried =int(98.8-1) but it didn't works. Because the answer would be 97

Dave Peterson wrote:
I had typos in my response--ignore that earlier version:

in A3:

=a2<a1

(you've got a typo in your explanation ("if A2 is less than A2").

Or
=a2a1
depending on what you really meant.

Or even
=a2=A1
or
=a2<=a1

(what happens when they're equal?)

(I changed all the A3's to A1's in the formulas)

thank you very much!!
But can u teach me one more thing? I'll really appreciate it.

[quoted text clipped - 4 lines]
How can i ask excel to add "false" in A3 if A2 is greater than A1?
And add "True" to A3, if A2 is less than A2



--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200601/1


  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
BenjieLop
 
Posts: n/a
Default Greater than, smaller than formula


nekteo via OfficeKB.com Wrote:
one more question...
You hv thought me how to make a value greater, but wat is the formula
to make
it smaller??
For example,
90 convert to 89
98.2 convert to 98
98.8 convert to 98
92.4 convert to 92

I tried =int(98.8-1) but it didn't works. Because the answer would be
97

Your formula should work.



--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=495258

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula Problem - interrupted by #VALUE! in other cells!? Ted Excel Worksheet Functions 17 November 25th 05 06:18 PM
Formula which is greater tahn or equal to zero bruce2444 Excel Worksheet Functions 2 September 7th 05 12:24 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 07:48 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 03:28 AM
Need a formula to determine the greater of a or b... sonyav Excel Discussion (Misc queries) 2 March 17th 05 09:13 PM


All times are GMT +1. The time now is 11:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"