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

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

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)

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

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.

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

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

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

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!!!

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

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

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

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

thanks... I don know wat to do without your help! I need to pass this
assignment next week!

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

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