Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 202
Default Excel cell conversion problem

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,101
Default Excel cell conversion problem

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,101
Default Excel cell conversion problem

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default Excel cell conversion problem

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 202
Default Excel cell conversion problem


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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 202
Default Excel cell conversion problem

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conversion problem Keithp Excel Discussion (Misc queries) 0 January 4th 07 12:01 PM
Date Conversion Problem sixwest Excel Discussion (Misc queries) 1 June 23rd 06 07:15 PM
Problem in Conversion usm Excel Discussion (Misc queries) 1 June 17th 06 06:32 PM
Excel Text Conversion Problem Michael D Excel Discussion (Misc queries) 0 March 14th 06 01:26 AM
Excel 97 to 2003 conversion problem PaulC Excel Discussion (Misc queries) 8 June 18th 05 01:12 PM


All times are GMT +1. The time now is 01:09 AM.

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

About Us

"It's about Microsoft Excel"