Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
To convert numbers into GCSE grades I need to use a formula that will read
down a column and if the cell content is between 1-7 to insert the letter U, if the cell content is between 8-11 to insert the letter G, if the cell content is between 12-15 to insert the letter F,etc Any ideas? |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Paste in an empty cpolumn and drag down. I haven't set up for checks outside
of the range of marks you specified i.e. 0 or (say) 99 so if this is a problem re-post =IF(A1<=7,"U",IF(A1<=11,"G","F")) Mike "Jane" wrote: To convert numbers into GCSE grades I need to use a formula that will read down a column and if the cell content is between 1-7 to insert the letter U, if the cell content is between 8-11 to insert the letter G, if the cell content is between 12-15 to insert the letter F,etc Any ideas? |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Jane,
Sorry, I didn't like it wothout the error checks so hers a version that checks for numbers in your range and for numeric input only. =IF(ISNUMBER(A1),IF(A1<=7,"U",IF(A1<=11,"G",IF(A1< =15,"F","Value out of range"))),"Value not numeric") Mike "Jane" wrote: To convert numbers into GCSE grades I need to use a formula that will read down a column and if the cell content is between 1-7 to insert the letter U, if the cell content is between 8-11 to insert the letter G, if the cell content is between 12-15 to insert the letter F,etc Any ideas? |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Mike's solution if good if you have only a small number of ranges. Because
you have etc I don't know how big your table really is. for large tabels the best way is to have a table of every number and letter(see table below). the formula to use would be VLOOKUP(). The formula become very simple to enter and get working. It may take more time to enter the table but it save time by eliminating debuging complex formulas. 1 U 2 U 3 U 4 U 5 U 6 U 7 U 8 G 9 G 10 G 11 G 12 F 13 F 14 F 15 F 16 17 18 19 "Jane" wrote: To convert numbers into GCSE grades I need to use a formula that will read down a column and if the cell content is between 1-7 to insert the letter U, if the cell content is between 8-11 to insert the letter G, if the cell content is between 12-15 to insert the letter F,etc Any ideas? |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() many thanks for your prompt reply. Toppers came up with =LOOKUP(D10,{1,8,12,16,20,24,28,32,36},{"U","G","F ","E","D","C","B","A","A*"}) which worked. "Joel" wrote: Mike's solution if good if you have only a small number of ranges. Because you have etc I don't know how big your table really is. for large tabels the best way is to have a table of every number and letter(see table below). the formula to use would be VLOOKUP(). The formula become very simple to enter and get working. It may take more time to enter the table but it save time by eliminating debuging complex formulas. 1 U 2 U 3 U 4 U 5 U 6 U 7 U 8 G 9 G 10 G 11 G 12 F 13 F 14 F 15 F 16 17 18 19 "Jane" wrote: To convert numbers into GCSE grades I need to use a formula that will read down a column and if the cell content is between 1-7 to insert the letter U, if the cell content is between 8-11 to insert the letter G, if the cell content is between 12-15 to insert the letter F,etc Any ideas? |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Many thanks Mike. Has a reply from Toppers who told me to try
=LOOKUP(D10,{1,8,12,16,20,24,28,32,36},{"U","G","F ","E","D","C","B","A","A*"}) which worked a treat so thanks for all your help. "Mike" wrote: Jane, Sorry, I didn't like it wothout the error checks so hers a version that checks for numbers in your range and for numeric input only. =IF(ISNUMBER(A1),IF(A1<=7,"U",IF(A1<=11,"G",IF(A1< =15,"F","Value out of range"))),"Value not numeric") Mike "Jane" wrote: To convert numbers into GCSE grades I need to use a formula that will read down a column and if the cell content is between 1-7 to insert the letter U, if the cell content is between 8-11 to insert the letter G, if the cell content is between 12-15 to insert the letter F,etc Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Conversion problem | Excel Discussion (Misc queries) | |||
Date Conversion Problem | Excel Discussion (Misc queries) | |||
Problem in Conversion | Excel Discussion (Misc queries) | |||
Excel Text Conversion Problem | Excel Discussion (Misc queries) | |||
Excel 97 to 2003 conversion problem | Excel Discussion (Misc queries) |