If cell contains then function

[AmyTaylor]

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'!F18&'T6'!F18&'T7'!F18&'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 "green", it is contains no As then it is a "amber", 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 "green", 3 or more Cs then it is "amber" and 3 or more D or E then its a "red".

All help greatly appreciated
Los of love
Amy xx

[systemx]

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,"A")
In F1 =COUNTIF(B1:F1,"B")
In G1 =COUNTIF(B1:F1,"C")
In H1 =COUNTIF(B1:F1,"D")
In I1=COUNTIF(B1:F1,"E")

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+F1>3,"green",IF(G1>3,"amber,IF(H1+I1>3,"red",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

[LanceB]

For the first condition

=(LEN(F18)-LEN(SUBSTITUTE(F18,"a",""))+LEN(F18)-LEN(SUBSTITUTE(F18,"b","")))>3

"AmyTaylor" wrote:

>
> 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'!F18&'T6'!F18&'T7'!F18&'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 "green", it is
> contains no As then it is a "amber", 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 "green", 3 or more Cs then it is "amber"
> and 3 or more D or E then its a "red".
>
> All help greatly appreciated
> Los of love
> Amy xx
>
>
> --
> AmyTaylor
> ------------------------------------------------------------------------
> AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970
> View this thread: http://www.excelforum.com/showthread...hreadid=539852
>
>

[Dav]

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,"a","")),"b",""))>=3 choose green as colour

Condition 2 =LEN(G3)-LEN(SUBSTITUTE((SUBSTITUTE(G3,"d","")),"e",""))>=3 choose amber

Condition 3 =LEN(G3)-LEN(SUBSTITUTE((SUBSTITUTE(G3,"d","")),"e",""))>=3 choose red

Regards

Dav