Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Greater than, smaller than formula
thanks... I don know wat to do without your help! I need to pass this
assignment next week! |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula which is greater tahn or equal to zero | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Need a formula to determine the greater of a or b... | Excel Discussion (Misc queries) |