Logical Filtering based on Value

  • Thread starter Thread starter caveman.savant
  • Start date Start date
C

caveman.savant

I've been using this formula to sort some values and return results

if(
or(
and(
b9<>"",
isnumber(
find(
Left(b9,1
,"AWFUY")
)
),
isnumber(
find("SHORT",g9
)
),
sum(
countif(c9,{"BOG","BLM","CMO"})
)
)
)
)
,i9-0.01,j9
)

--I used ALT-ENTER between each formula so as to see thing clearly
Column A values can be "B01"
Column B values can be "BRN"
Columns I & J are values, one being lowest the other being highest
allowed.




On another sheet I use
IF(
OR(
AND(
B15<>"",
ISNUMBER(
FIND(
LEFT(
B15,1),"JCR"
)
),
),
SUM(
COUNTIF(
C15,{"DST","LVN","DNM"}
)
),
),
I15-0.01,J15
)

1st:The "JCR" formula is not quite working. With "R08" in column B and
"DNM" in column C, for example things work great. But "B73" in A and
"DST" in C I get the wrong result. (values starting with "B" should be
filtered)

2nd: I would like to combine this into one formula
 
Seeking clarification. So far I have
=IF(OR(AND( B9<>"", ISNUMBER( FIND(LEFT(B9,1),"AWFUL") )),ISNUMBER(
FIND("SHORT",G9 ))),"A","B")

Please explain what is to happen if the condition is TRUE (A) and what is to
happen if FALSE (B)

Also unclear why we are told: Column A values can be "B01" ; Column B values
can be "BRN

best wishes
 
If the condition is true value of cell in column "I"-0.01, if false
value of cell in column "J"
 
IF(OR(AND( B9<>"", ISNUMBER( FIND(LEFT(B9,1),"AWFUL") )),ISNUMBER(
FIND("SHORT",G9 ))),I9-0,01,J9)


--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

If the condition is true value of cell in column "I"-0.01, if false
value of cell in column "J"
 
Back
Top