Single Condition Problem

rushthecourt

New Member
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.

RTC

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

xenou

MrExcel MVP
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.

Replies
7
Views
1K
Replies
12
Views
885
Replies
2
Views
310
Replies
3
Views
412
Replies
0
Views
2K

1,181,659
Messages
5,931,273
Members
436,786
Latest member
Deniel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back