Solved: IF AND PROBLEM

[jammer6_9]

Hi all,

Whats the problem of my formula? I have 9 logical conditions. The first 6 is okay but on the last 3 I am facing difficulties. Is there any limitation of using AND? Formula below is pointing on 2nd AND of the last 3 conditions...


=IF(AND(B58="L",M58<=0.15%),1090,IF(AND(B58="L",M58<=0.20%),1045,IF(AND(B58 ="L",M58<=0.25%),1000,IF(AND(B58="M",M58<=0.15%),840,IF(AND(B58="M",M58<=0. 20%),795,IF(AND(B58="M",M58<=0.25%),750,IF(AND(B58="S",M58<=0.15%),500,IF(AND(B58="S",M58<=0.20%,545,IF(AND(B58="S",M58<=0.25%),500,"")))))))))

[jammer6_9]

Remarks (EXCEL HELP)

• Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests.

[mdmackillop]

Have a look at C Pearsons solution

[jammer6_9]

Have a look at C Pearsons solution

Create a name to represent a formula or a constant

1. On the Insert menu, point to Name, and then click Define.
2. In the Names in workbook box, enter the name for the formula.
3. In the Refers to box, type = (equal sign), followed by the formula or the constant (constant: A value that is not calculated and, therefore, does not change. For example, the number 210, and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.) value.

Exact Solution... Thanks!

[Bob Phillips]

=IF(OR(M58>0.25%,SUM(--COUNTIF(B58,{"L","M","S"}))=0),"",
N(SUM(COUNTIF(B58,{"L","M","S"})*{1000,750,500},LOOKUP(M58,{0,0.0015001,0.0 02001},{90,45,0}))))

[jammer6_9]

=IF(OR(M58>0.25%,SUM(--COUNTIF(B58,{"L","M","S"}))=0),"",
N(SUM(COUNTIF(B58,{"L","M","S"})*{1000,750,500},LOOKUP(M58,{0,0.0015001,0.0 02001},{90,45,0}))))

You are a xld ... You simplify things... Formula is perfect and you just made me genius to my Boss

[asingh]

=IF(OR(M58>0.25%,SUM(--COUNTIF(B58,{"L","M","S"}))=0),"",
N(SUM(COUNTIF(B58,{"L","M","S"})*{1000,750,500},LOOKUP(M58,{0,0.0015001,0.0 02001},{90,45,0}))))

Will COUNTIF(B58,{"L","M","S"}) work as a single formula on a given range..

For exampe:COUNTIF(B5:B8,{"a","b","c"})

and it returns me the count of existence of a,b,c in my range.

I tried it..it gives a one each time..??

[Bob Phillips]

The COUNTIF will return an array, so you are just seeing the first item in the array. To count them all, you have to sum the array

=SUM(COUNTIF(B5:B8,{"a","b","c"}))

[asingh]

Perfect..works..!

Great -- thanks.