Assigning a number value to strings
Example: (see explantion below)
Column A Answer 1 LMS LADprox 3 (2 for LMS,0 for LAD prox and LADother,1 for RCA) LAD other RCA 2 LADprox 1 (1 for LAD prox, 0 for LADother) LADother 3 LADother 2 (2 for LMS, 0 for LADother) LMS 4 RCA 2 (1 for RCA, 1 for LCX) LCx 5 LMS 3 (2 for LMS, 1 for RCA) RCA 6 LADother LCX (1 for LADother, 1 for LCX, 0 for LAD prox) LAD prox I have given an example above. I need to asign a values to the various strings. I want LMS to equal 2, LADprox to = 1, LADother to =1, LCx to =1, RCA to = 1. However, if the cell contains LMS, then I do not want the values for LADprox, LADother and LCx to be counted (but I still want to count RCA as =1) Also if cell contains LADprox and LADother I only want to count this as 1 (unless the cell also contains LMS in which case they would count as 0) Does this make sense to anyone!! If so, how can I do this with excel 2007? Thanks John |
Assigning a number value to strings
A bit of twain brister. But I think I have the solution. It does give the
same results as your examples when tested against them. One note: make sure you are consistent in the way the "LADother" and "LADprox" are spelled in column A. In some cases you have a space after LAD and in others you don't. The formula is set up without the space. Here's my formula for row 1: =IF(ISERR(FIND("LMS",A1)),IF(ISERR(FIND("RCA",A1)) ,IF(ISERR(FIND("LADprox",A1)),IF(ISERR(FIND("LADot her",A1)),IF(ISERR(FIND("RCA",A1)),0,1) + IF(ISERR(FIND("LCX",A1)),0,1),IF(ISERR(FIND("RCA", A1)),0,1) + IF(ISERR(FIND("LCX",A1)),0,1)+IF(ISERR(FIND("LADpr ox",A1)),0,1)+IF(ISERR(FIND("LADother",A1)),0,1)), IF(ISERR(FIND("LADother",A1)),IF(ISERR(FIND("RCA", A1)),0,1) + IF(ISERR(FIND("LCX",A1)),0,1)+IF(ISERR(FIND("LADpr ox",A1)),0,1)+IF(ISERR(FIND("LADother",A1)),0,1) ,1 + IF(ISERR(FIND("LCX",A1)),0,1 + IF(ISERR(FIND("RCA",A1)),0,1)))),1 + IF(ISERR(FIND("LCX",A1)),0,1)),IF(ISERR(FIND("RCA" ,A1)),2,2+1)) You'll notice in some places I used result values like 2+1 instead of just plain 3. That is so if you need to change the value of various strings, it will be easier. Here it is with words in place of returned values, this may help you with future changes also since it shows you where decisions are made and what those are. =IF(ISERR(FIND("LMS",A2)),IF(ISERR(FIND("RCA",A2)) ,IF(ISERR(FIND("LADprox",A2)),IF(ISERR(FIND("LADot her",A2)),IF(ISERR(FIND("RCA",A2)),0,1) + IF(ISERR(FIND("LCX",A2)),0,1),IF(ISERR(FIND("RCA", A2)),0,1) + IF(ISERR(FIND("LCX",A2)),0,1)+IF(ISERR(FIND("LADpr ox",A2)),0,1)+IF(ISERR(FIND("LADother",A2)),0,1)), IF(ISERR(FIND("LADother",A2)),IF(ISERR(FIND("RCA", A2)),0,1) + IF(ISERR(FIND("LCX",A2)),0,1)+IF(ISERR(FIND("LADpr ox",A2)),0,1)+IF(ISERR(FIND("LADother",A2)),0,1)," LADo and LADp = 1 + any LCX and/or RCA")),"LMS=no,RCA=yes = 1"),IF(ISERR(FIND("RCA",A2)),"LMS only = 2","LMA and RCA = 2+1")) "JRD" wrote: Example: (see explantion below) Column A Answer 1 LMS LADprox 3 (2 for LMS,0 for LAD prox and LADother,1 for RCA) LAD other RCA 2 LADprox 1 (1 for LAD prox, 0 for LADother) LADother 3 LADother 2 (2 for LMS, 0 for LADother) LMS 4 RCA 2 (1 for RCA, 1 for LCX) LCx 5 LMS 3 (2 for LMS, 1 for RCA) RCA 6 LADother LCX (1 for LADother, 1 for LCX, 0 for LAD prox) LAD prox I have given an example above. I need to asign a values to the various strings. I want LMS to equal 2, LADprox to = 1, LADother to =1, LCx to =1, RCA to = 1. However, if the cell contains LMS, then I do not want the values for LADprox, LADother and LCx to be counted (but I still want to count RCA as =1) Also if cell contains LADprox and LADother I only want to count this as 1 (unless the cell also contains LMS in which case they would count as 0) Does this make sense to anyone!! If so, how can I do this with excel 2007? Thanks John |
All times are GMT +1. The time now is 08:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com