If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Greater than, smaller than formula
 Author Name Remember Me? Password
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Greater than, smaller than formula

 Thread Tools Display Modes
#1
December 21st 05, 05:50 PM posted to microsoft.public.excel.worksheet.functions
 alexander_geoff external usenet poster Posts: n/a
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

 Ads
#2
December 21st 05, 06:03 PM posted to microsoft.public.excel.worksheet.functions
 BenjieLop external usenet poster Posts: n/a
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
December 21st 05, 06:06 PM posted to microsoft.public.excel.worksheet.functions
 Harlan Grove external usenet poster Posts: n/a
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
December 21st 05, 10:02 PM posted to microsoft.public.excel.worksheet.functions
 alexander_geoff external usenet poster Posts: n/a
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
December 21st 05, 10:52 PM posted to microsoft.public.excel.worksheet.functions
 Gord Dibben external usenet poster Posts: n/a
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
December 22nd 05, 06:20 AM posted to microsoft.public.excel.worksheet.functions
 BenjieLop external usenet poster Posts: n/a
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
December 22nd 05, 05:50 PM posted to microsoft.public.excel.worksheet.functions
 alexander_geoff external usenet poster Posts: n/a
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
December 22nd 05, 11:21 PM posted to microsoft.public.excel.worksheet.functions
 BenjieLop external usenet poster Posts: n/a
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
December 29th 05, 03:53 PM posted to microsoft.public.excel.worksheet.functions
 nekteo external usenet poster Posts: n/a
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
December 29th 05, 03:59 PM posted to microsoft.public.excel.worksheet.functions
 Dave Peterson external usenet poster Posts: n/a
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

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 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 05: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 08:13 PM

All times are GMT +1. The time now is 08:20 AM.

 - Contact Us - ExcelBanter forum home - FAQ - Links - Privacy Statement - Top

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