Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Text into Values
I have a column of data in which recent test results are recorded. Column A
specifies the shop and in column B the data is recorded in the format 'AARAGG' where R = Red, A = Amber and G = Green. These are, in effect, the 6 most recent test results, with the latest on the right of the text string. Usually there will be six results recorded, but sometimes there could be less and occasionally more (never more than 8). What I need to do is to come up with a formula that converts the text into a numeric value, reflecting the risk at each unit. Hence, Red needs to be 3 points, Amber 2 and Green 1. Also, there needs to be a weighting towards the most recent test such that the right hand value is multiplied by 10, the next from right 9, and so on. So, in the example above, counting from the right: G = 1 * 10 = 10 G = 1 * 9 = 9 A = 2 * 8 = 16 R = 3 * 7 = 21 A = 2 * 6 = 12 A = 2 * 5 = 10 So the total returned in column C would be 78. Any thoughts/ideas on how I can achieve this? Many thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Text into Values
With AARAGG in B2, put this in C2:
=SUMPRODUCT(LOOKUP(RIGHT(B2,ROW(A$1:A$6)),{"A","G" ,"R"},{2,1,3})*(11- ROW(A$1:A$6))) This assumes that you will always have 6 characters in column B. I'll work on it a bit more to see if I can tweak it to respond to fewer characters. Hope this helps. Pete On Aug 26, 10:19*pm, "Terry Bennett" wrote: I have a column of data in which recent test results are recorded. *Column A specifies the shop and in column B the data is recorded in the format 'AARAGG' where R = Red, A = Amber and G = Green. *These are, in effect, the 6 most recent test results, with the latest on the right of the text string. Usually there will be six results recorded, but sometimes there could be less and occasionally more (never more than 8). What I need to do is to come up with a formula that converts the text into a numeric value, reflecting the risk at each unit. *Hence, Red needs to be 3 points, Amber 2 and Green 1. *Also, there needs to be a weighting towards the most recent test such that the right hand value is multiplied by 10, the next from right 9, and so on. So, in the example above, counting from the right: G = 1 * 10 = 10 G = 1 * 9 = 9 A = 2 * 8 = 16 R = 3 * 7 = 21 A = 2 * 6 = 12 A = 2 * 5 = 10 So the total returned in column C would be 78. Any thoughts/ideas on how I can achieve this? Many thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Text into Values
Try this array formula**.
=IF(B2="","",SUM(FIND(MID(B2,ROW(INDIRECT("1:"&LEN (B2))),1),"GAR")*ROW(INDIRECT(10-LEN(B2)+1&":10")))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Terry Bennett" wrote in message ... I have a column of data in which recent test results are recorded. Column A specifies the shop and in column B the data is recorded in the format 'AARAGG' where R = Red, A = Amber and G = Green. These are, in effect, the 6 most recent test results, with the latest on the right of the text string. Usually there will be six results recorded, but sometimes there could be less and occasionally more (never more than 8). What I need to do is to come up with a formula that converts the text into a numeric value, reflecting the risk at each unit. Hence, Red needs to be 3 points, Amber 2 and Green 1. Also, there needs to be a weighting towards the most recent test such that the right hand value is multiplied by 10, the next from right 9, and so on. So, in the example above, counting from the right: G = 1 * 10 = 10 G = 1 * 9 = 9 A = 2 * 8 = 16 R = 3 * 7 = 21 A = 2 * 6 = 12 A = 2 * 5 = 10 So the total returned in column C would be 78. Any thoughts/ideas on how I can achieve this? Many thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Text into Values
Ah well, that didn't take too long. Put this in C2:
=SUMPRODUCT(LOOKUP(RIGHT(B2,ROW(INDIRECT("A1:A"&LE N(B2)))), {"A","G","R"},{2,1,3})*(11-ROW(INDIRECT("A1:A"&LEN(B2))))) (all one formula - be wary of spurious line breaks) Copy this down column C for as far as you require. Hope this helps. Pete On Aug 26, 10:19*pm, "Terry Bennett" wrote: I have a column of data in which recent test results are recorded. *Column A specifies the shop and in column B the data is recorded in the format 'AARAGG' where R = Red, A = Amber and G = Green. *These are, in effect, the 6 most recent test results, with the latest on the right of the text string. Usually there will be six results recorded, but sometimes there could be less and occasionally more (never more than 8). What I need to do is to come up with a formula that converts the text into a numeric value, reflecting the risk at each unit. *Hence, Red needs to be 3 points, Amber 2 and Green 1. *Also, there needs to be a weighting towards the most recent test such that the right hand value is multiplied by 10, the next from right 9, and so on. So, in the example above, counting from the right: G = 1 * 10 = 10 G = 1 * 9 = 9 A = 2 * 8 = 16 R = 3 * 7 = 21 A = 2 * 6 = 12 A = 2 * 5 = 10 So the total returned in column C would be 78. Any thoughts/ideas on how I can achieve this? Many thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Text into Values
Pete/Biff
Excellent - both of these suggestions work fine! I'm very grateful and will spend the next few days trying to work-out what the finer points of the formulae mean! Terry "Pete_UK" wrote in message ... Ah well, that didn't take too long. Put this in C2: =SUMPRODUCT(LOOKUP(RIGHT(B2,ROW(INDIRECT("A1:A"&LE N(B2)))), {"A","G","R"},{2,1,3})*(11-ROW(INDIRECT("A1:A"&LEN(B2))))) (all one formula - be wary of spurious line breaks) Copy this down column C for as far as you require. Hope this helps. Pete On Aug 26, 10:19 pm, "Terry Bennett" wrote: I have a column of data in which recent test results are recorded. Column A specifies the shop and in column B the data is recorded in the format 'AARAGG' where R = Red, A = Amber and G = Green. These are, in effect, the 6 most recent test results, with the latest on the right of the text string. Usually there will be six results recorded, but sometimes there could be less and occasionally more (never more than 8). What I need to do is to come up with a formula that converts the text into a numeric value, reflecting the risk at each unit. Hence, Red needs to be 3 points, Amber 2 and Green 1. Also, there needs to be a weighting towards the most recent test such that the right hand value is multiplied by 10, the next from right 9, and so on. So, in the example above, counting from the right: G = 1 * 10 = 10 G = 1 * 9 = 9 A = 2 * 8 = 16 R = 3 * 7 = 21 A = 2 * 6 = 12 A = 2 * 5 = 10 So the total returned in column C would be 78. Any thoughts/ideas on how I can achieve this? Many thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Text into Values
You're welcome, Terry - thanks for feeding back.
Pete On Aug 26, 11:59*pm, "Terry Bennett" wrote: Pete/Biff Excellent - both of these suggestions work fine! *I'm very grateful and will spend the next few days trying to work-out what the finer points of the formulae mean! Terry "Pete_UK" wrote in message ... Ah well, that didn't take too long. Put this in C2: =SUMPRODUCT(LOOKUP(RIGHT(B2,ROW(INDIRECT("A1:A"&LE N(B2)))), {"A","G","R"},{2,1,3})*(11-ROW(INDIRECT("A1:A"&LEN(B2))))) (all one formula - be wary of spurious line breaks) Copy this down column C for as far as you require. Hope this helps. Pete On Aug 26, 10:19 pm, "Terry Bennett" wrote: I have a column of data in which recent test results are recorded. Column A specifies the shop and in column B the data is recorded in the format 'AARAGG' where R = Red, A = Amber and G = Green. These are, in effect, the 6 most recent test results, with the latest on the right of the text string. Usually there will be six results recorded, but sometimes there could be less and occasionally more (never more than 8). What I need to do is to come up with a formula that converts the text into a numeric value, reflecting the risk at each unit. Hence, Red needs to be 3 points, Amber 2 and Green 1. Also, there needs to be a weighting towards the most recent test such that the right hand value is multiplied by 10, the next from right 9, and so on. So, in the example above, counting from the right: G = 1 * 10 = 10 G = 1 * 9 = 9 A = 2 * 8 = 16 R = 3 * 7 = 21 A = 2 * 6 = 12 A = 2 * 5 = 10 So the total returned in column C would be 78. Any thoughts/ideas on how I can achieve this? Many thanks.- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Text into Values
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Terry Bennett" wrote in message ... Pete/Biff Excellent - both of these suggestions work fine! I'm very grateful and will spend the next few days trying to work-out what the finer points of the formulae mean! Terry "Pete_UK" wrote in message ... Ah well, that didn't take too long. Put this in C2: =SUMPRODUCT(LOOKUP(RIGHT(B2,ROW(INDIRECT("A1:A"&LE N(B2)))), {"A","G","R"},{2,1,3})*(11-ROW(INDIRECT("A1:A"&LEN(B2))))) (all one formula - be wary of spurious line breaks) Copy this down column C for as far as you require. Hope this helps. Pete On Aug 26, 10:19 pm, "Terry Bennett" wrote: I have a column of data in which recent test results are recorded. Column A specifies the shop and in column B the data is recorded in the format 'AARAGG' where R = Red, A = Amber and G = Green. These are, in effect, the 6 most recent test results, with the latest on the right of the text string. Usually there will be six results recorded, but sometimes there could be less and occasionally more (never more than 8). What I need to do is to come up with a formula that converts the text into a numeric value, reflecting the risk at each unit. Hence, Red needs to be 3 points, Amber 2 and Green 1. Also, there needs to be a weighting towards the most recent test such that the right hand value is multiplied by 10, the next from right 9, and so on. So, in the example above, counting from the right: G = 1 * 10 = 10 G = 1 * 9 = 9 A = 2 * 8 = 16 R = 3 * 7 = 21 A = 2 * 6 = 12 A = 2 * 5 = 10 So the total returned in column C would be 78. Any thoughts/ideas on how I can achieve this? Many thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting values to text | New Users to Excel | |||
Converting a date to a text field w/o converting it to a julian da | Excel Worksheet Functions | |||
Converting Numeric values to Text | Excel Worksheet Functions | |||
Converting Text Values to Dates | Excel Discussion (Misc queries) | |||
Stop Excel from converting text labels in CSV files to Values | Excel Discussion (Misc queries) |