software

Hope someone can help with this function query I have:

I have the following function in cell F18 of my total sheet, it is a
concatenation of 4 cells on sheets T10, T6, T7 and T9:
CONCATENATE('T10'!F18amp;'T6'!F18amp;'T7'!F18amp;'T9'!F18)
The answer will be a combination of A,B,C,D or E.
What I would like is this, if possible:
If the concatenation contains mostly A or B then it is a quot;greenquot;, it is
contains no As then it is a quot;amberquot;, if it contains mostly D or E then
it is a red.
If we need to specify the actual number of characters, then it would
be:
3 or more As or Bs and it is a quot;greenquot;, 3 or more Cs then it is quot;amberquot;
and 3 or more D or E then its a quot;redquot;.

All help greatly appreciated
Los of love
Amy xx--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile: www.excelforum.com/member.php...oamp;userid=20970
View this thread: www.excelforum.com/showthread...hreadid=539852
Hi Amy,

Someone else may know a better way to do this. But here is the
long-winded way!

Lets say cell A1 contains your text string (ABCDE)

In cell B1 use =LEFT(A1,1)
In cell C1 use =MID(A1,2,1)
In cell D1 use =MID(A1,3,1)
In cell E1 use =MID(A1,4,1)
In cell F1 use =MID(A1,5,1)

To split the concantenated string into individual cells.

Then

In E1 =COUNTIF(B1:F1,quot;Aquot;)
In F1 =COUNTIF(B1:F1,quot;Bquot;)
In G1 =COUNTIF(B1:F1,quot;Cquot;)
In H1 =COUNTIF(B1:F1,quot;Dquot;)
In I1=COUNTIF(B1:F1,quot;Equot;)

This will give you a raw count of how many A's, B's, C's, D's and E's.

Last but not least....the final cell....

=IF(E1 F1gt;3,quot;greenquot;,IF(G1gt;3,quot;amber,IF(H1 I1gt;3,quot;red quot;,0)))

Of course this will only return the colour as a text string inside your
calculation cell. Simply replace 'green', 'amber' and 'red' with
whatever values you want, then use conditional formatting to colour
appropriately.

There is probably a much smarter way to do this.....hope someone finds
it for you

Regards

Rob--
systemx
------------------------------------------------------------------------
systemx's Profile: www.excelforum.com/member.php...oamp;userid=29254
View this thread: www.excelforum.com/showthread...hreadid=539852For the first condition

=(LEN(F18)-LEN(SUBSTITUTE(F18,quot;aquot;,quot;quot;)) LEN(F18)-LEN(SUBSTITUTE(F18,quot;bquot;,quot;quot;)))gt;3

quot;AmyTaylorquot; wrote:

gt;
gt; Hope someone can help with this function query I have:
gt;
gt; I have the following function in cell F18 of my total sheet, it is a
gt; concatenation of 4 cells on sheets T10, T6, T7 and T9:
gt; CONCATENATE('T10'!F18amp;'T6'!F18amp;'T7'!F18amp;'T9'!F18)
gt; The answer will be a combination of A,B,C,D or E.
gt; What I would like is this, if possible:
gt; If the concatenation contains mostly A or B then it is a quot;greenquot;, it is
gt; contains no As then it is a quot;amberquot;, if it contains mostly D or E then
gt; it is a red.
gt; If we need to specify the actual number of characters, then it would
gt; be:
gt; 3 or more As or Bs and it is a quot;greenquot;, 3 or more Cs then it is quot;amberquot;
gt; and 3 or more D or E then its a quot;redquot;.
gt;
gt; All help greatly appreciated
gt; Los of love
gt; Amy xx
gt;
gt;
gt; --
gt; AmyTaylor
gt; ------------------------------------------------------------------------
gt; AmyTaylor's Profile: www.excelforum.com/member.php...oamp;userid=20970
gt; View this thread: www.excelforum.com/showthread...hreadid=539852
gt;
gt;


If the cell you are interested in is g3 enter the following as formulas
in conditional formating

Condition 1
=LEN(G3)-LEN(SUBSTITUTE((SUBSTITUTE(G3,quot;aquot;,quot;quot;)),quot;bquot;,quot;quot;))gt;=3 choose
green as colour

Condition 2 =LEN(G3)-LEN(SUBSTITUTE((SUBSTITUTE(G3,quot;dquot;,quot;quot;)),quot;equot;,quot;quot;))gt;=3
choose amber

Condition 3 =LEN(G3)-LEN(SUBSTITUTE((SUBSTITUTE(G3,quot;dquot;,quot;quot;)),quot;equot;,quot;quot;))gt;=3
choose red

Regards

Dav--
Dav
------------------------------------------------------------------------
Dav's Profile: www.excelforum.com/member.php...oamp;userid=27107
View this thread: www.excelforum.com/showthread...hreadid=539852