Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If cell contains then function
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If cell contains then function
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+F13,"green",IF(G13,"amber,IF(H1+I13,"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 -- systemx ------------------------------------------------------------------------ systemx's Profile: http://www.excelforum.com/member.php...o&userid=29254 View this thread: http://www.excelforum.com/showthread...hreadid=539852 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If cell contains then function
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If cell contains then function
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 -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=539852 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can function in one cell change value or function in another cell? | Excel Worksheet Functions | |||
Function that Returns address of that cell? | Excel Worksheet Functions | |||
Reading Cell Function??? | Excel Worksheet Functions | |||
Function making cell really "empty" | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |