Single Condition Problem

rushthecourt

New Member
Joined
Jul 8, 2007
Messages
35
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
 

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
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top