Solved Large with a condition

Microsoft Excel 2003 (full product)
September 21, 2011 at 10:38:04
Specs: Windows XP
I have a column of numbers (named Unique_ID) in Sheet A.
In Sheet B, I want to pull all the numbers valued between 1 and 2 (not inclusive).

=LARGE((AND(Unique_ID>1,Unique_ID<2)),1)
=LARGE((AND(Unique_ID>1,Unique_ID<2)),2)
...

I get #Value

What is wrong with my logic?


See More: Large with a condition

Report •


#1
September 21, 2011 at 11:56:49
As long as the Rows in which your formulas reside in Sheet B correspond with the Rows in Sheet A, this should work:

=IF(AND(Unique_ID>1,Unique_ID<2),Unique_ID,"")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
September 21, 2011 at 12:20:21
Thanks DerbyDad, this kind of works... But when I drag down the formula, it doesn't populate them from largest to smallest and I get a bunch of blank values.

Is there no way that I can use LARGE as a drag down formula to pull the 1st, 2nd, 3rd, etc largest of the list that are between values 1 and 2?

EX:
Sheet A:

1.00002
1.00050
2.00050
2.00001


I want "sheet B" to be:

1.00050
1.00002

My goal is later to have other sheets only publish between 2-3 then 3-4 etc.


Report •

#3
September 21, 2011 at 13:22:56
✔ Best Answer
Well, what you asked for in your first post doesn't provide all of the detail that you provided in your second.

You merely asked how to "pull all the numbers valued between 1 and 2" without any mention of ranking them or deleting blank rows.

We don't read minds, ya know! ;-)

Anyway, I can't think of any way to accomplish your goal with built in Excel functions.

I've seen some UDF's (User Defined Functions, written in VBA) that will return the MAX value between 2 values, but nothing that RANKS a range of values between 2 values. I suppose one could be written to do it, but it would be tricky.

Have you considered using an Advanced Filter with Criteria?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions


Ask Question