Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 67
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 67
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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
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
Converting values to text Keith Rathband New Users to Excel 3 October 4th 08 11:18 PM
Converting a date to a text field w/o converting it to a julian da LynnMinn Excel Worksheet Functions 2 March 6th 08 04:43 PM
Converting Numeric values to Text shail Excel Worksheet Functions 2 September 5th 06 04:50 PM
Converting Text Values to Dates Frank Winston Excel Discussion (Misc queries) 12 June 5th 06 11:35 AM
Stop Excel from converting text labels in CSV files to Values Just Want a Label! Excel Discussion (Misc queries) 1 January 11th 05 05:51 PM


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

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

About Us

"It's about Microsoft Excel"