PDA

View Full Version : Solved: IF AND PROBLEM



jammer6_9
07-26-2008, 10:52 PM
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
07-27-2008, 01:03 AM
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. :mkay

mdmackillop
07-27-2008, 02:21 AM
Have a look at C Pearsons solution (http://www.cpearson.com/Excel/nested.htm)

jammer6_9
07-27-2008, 03:20 AM
Have a look at C Pearsons solution (http://www.cpearson.com/Excel/nested.htm)

Create a name to represent a formula or a constant


On the Insert menu, point to Name, and then click Define.
In the Names in workbook box, enter the name for the formula.
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.) (mk:@MSITStore:C:\Program%20Files\Microsoft%20Office\OFFICE11\1033\xlmain11 .chm::/html/xlhowNameaformulaorreference1.htm#) value.
:whistle: Exact Solution... Thanks!

Bob Phillips
07-27-2008, 04:24 AM
=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.002001},{90,45,0}))))

jammer6_9
07-27-2008, 05:31 AM
=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.002001},{90,45,0}))))

You are a :devil2: xld :bug: ... :ipray: You simplify things... Formula is perfect and you just made me genius to my Boss :friends:

asingh
07-27-2008, 12:44 PM
=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.002001},{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
07-27-2008, 03:13 PM
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
07-28-2008, 03:38 AM
Perfect..works..!

Great -- thanks.