Greater than, smaller than formula

[alexander_geoff]

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.

 

[BenjieLop]

When grading children's test scores I want to apply letters and numeral
to particular ranges eg between 21 and 25=3c 26and 30=3b. Please hel
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

 

[Harlan Grove]

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)

 

[alexander_geoff]

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.

 

[Gord Dibben]

Alexander

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

Adjust to suit more ranges if desired.


Gord Dibben Excel MVP

 

[BenjieLop]

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.

 

[alexander_geoff]

Worked thanks

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]

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

Regards and Merry Christmas to you and yours

 

[nekteo]

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]

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

=int(a1+1)

If A1 held the original number.

 

[nekteo]

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]

in A3:

=a2<a3

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

Or
=a2>a3
depending on what you really meant.

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

(what happens when they're equal?)

 

[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
=a2>a1
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]

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

 

[nekteo via OfficeKB.com]

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

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
=a2>a1
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

 

[BenjieLop]

one more question...
You hv thought me how to make a value greater, but wat is the formul
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 b
97 >>

Your formula should work