Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 21st 05, 06:50 PM 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  
Old December 21st 05, 07:03 PM 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  
Old December 21st 05, 07:06 PM 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  
Old December 21st 05, 11:02 PM 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  
Old December 21st 05, 11:52 PM 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
<[email protected] 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  
Old December 22nd 05, 07:20 AM 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  
Old December 22nd 05, 06:50 PM 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  
Old December 23rd 05, 12:21 AM 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  
Old December 29th 05, 04:53 PM 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  
Old December 29th 05, 04:59 PM 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


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 10:36 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017