Single Condition Problem

[rushthecourt]

Hi -

Using the following formula:

=SUM(IF(ROW(C4:C4997)>=LARGE(IF(C4:C4997="x",IF(K4:K4997<>"",ROW(C4:C4997))),4),IF(C4:C4997="x",IF(E4:E4997="A",IF(K4:K4997<>"",1)))))&"-"&SUM(IF(ROW(C4:C4997)>=LARGE(IF(C4:C4997="x",IF(K4:K4997<>"",ROW(C4:C4997))),4),IF(C4:C4997="n",IF(E4:E4997="B",IF(K4:K4997<>"",1)))))


I am trying to look the last 4 times col. C = x. If there are 4 or more times where col. C = x, then it gives me a notation that I want (such as 3-1, or 2-2).

The problem arises when there are <4 times where col. C = x. In those cases I'm getting the #NUM! error, rather than the notation 2-1 or 0-3, for example.

Can someone please help?! Thanks.

RTC

 

[xenou]

Hi:
You have set 4 as the k-th largest factor for the large function. You must have at least four x's.

It's a little hard to visualize what this formula does. What is in Columns K and E and how to the relate to column C. What are you trying to do? What does the 3-1, 2-2 etc. mean?

As is, to create a formula that works for less than four x's you'll have to first test for the existence of at least four x's and then change argument for the Large function based on this condition. You may want to set another cell with the formula IF(countif(C:C,"x")<4,countif(C:C,"x"),4) then use that cell as an absolute reference in your formula above.

Regards.

edit: note, also, determine what the large formula does when k = 0. I haven't used large much so offhand I just don't know.