Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I show text that represents a number
I want to take a table that shows text in a column for several rows, but the
text in each row represents a number that I can average? Here's the table - If I want the A+ to represent a number when O enter A+, how do I do that?: A+ 4.5 A 4 B+ 3.5 B 3 C+ 2.5 C 2 D+ 1.5 D 1 F 0 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I show text that represents a number
Let's say that your table is in A1:B8
In C1, I enter some text like: C+ In D1 I can use =VLOOKUP(C1,A1:B8,2,FALSE) to give me the result 2.5 Of course, your table could be on another sheet and then we use =VLOOKUP(C1,Sheet3!A1:B9,2,FALSE) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Ron" wrote in message ... I want to take a table that shows text in a column for several rows, but the text in each row represents a number that I can average? Here's the table - If I want the A+ to represent a number when O enter A+, how do I do that?: A+ 4.5 A 4 B+ 3.5 B 3 C+ 2.5 C 2 D+ 1.5 D 1 F 0 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I show text that represents a number
try this
=SUMPRODUCT(COUNTIF(A1:A10,{"A+";"A";"B+";"B";"C+" ;"C";"D+";"D";"F"}),{4.5;4;3.5;3;2.5;2;1.5;1;0} )/COUNTA(A1:A10) Change the range to suit. Any text other that the grades in the range will give an incorrect result. Mike "Ron" wrote: I want to take a table that shows text in a column for several rows, but the text in each row represents a number that I can average? Here's the table - If I want the A+ to represent a number when O enter A+, how do I do that?: A+ 4.5 A 4 B+ 3.5 B 3 C+ 2.5 C 2 D+ 1.5 D 1 F 0 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I show text that represents a number
For letter grades in column A:
=(COUNTIF(A:A,"A+")*4.5+COUNTIF(A:A,"A")*4+COUNTIF (A:A,"B+")*3.5+COUNTIF(A:A,"B")*3+COUNTIF(A:A,"C+" )*2.5+COUNTIF(A:A,"C")*2+COUNTIF(A:A,"D+")*1.5+COU NTIF(A:A,"D"))/COUNTA(A:A) Thus if column A contained: A A+ C C+ the formula returns 3.25 -- Gary''s Student - gsnu200806 "Ron" wrote: I want to take a table that shows text in a column for several rows, but the text in each row represents a number that I can average? Here's the table - If I want the A+ to represent a number when O enter A+, how do I do that?: A+ 4.5 A 4 B+ 3.5 B 3 C+ 2.5 C 2 D+ 1.5 D 1 F 0 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I show text that represents a number
Try this:
=MAX(69-CODE(UPPER(A1)),0)+(LEN(A1)=2)*0.5 "Ron" wrote: I want to take a table that shows text in a column for several rows, but the text in each row represents a number that I can average? Here's the table - If I want the A+ to represent a number when O enter A+, how do I do that?: A+ 4.5 A 4 B+ 3.5 B 3 C+ 2.5 C 2 D+ 1.5 D 1 F 0 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
understanding what part of a formula represents | Excel Worksheet Functions | |||
Letter represents a number | Excel Discussion (Misc queries) | |||
Converting time as 7.5 (which represents 7:30 AM) to 0730 | Excel Worksheet Functions | |||
grab cell text from multi-tab workbook, show text in another workb | Excel Worksheet Functions | |||
Taking out string part which represents date | Excel Worksheet Functions |