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.