Formula to find values =>2 points from lowest value within a range

Hi,
I would like a formula that determines when there is a 2+ point gap between
lowest value and second lowest value within a range.
Please see spreadsheet attached
Thanks
Ian
Valuesgreaterequal-to-two.xlsx
raceproretiredAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hi Ian,

Please try this and see if you get the desired output.
I have added the records in the attached you mentioned in your last post.

=AND(SMALL(IF($A$2:$A$41=A2,$B$2:$B$41),1)<=3.5,(SMALL(IF(FREQUENCY(IF($A$2:$A$41=A2,$B$2:$B$41),$B$2:$B$41),$B$2:$B$41),2)-SMALL(IF($A$2:$A$41=A2,$B$2:$B$41),1))>=2)*1

Open in new window

Valuesgreaterequal-to-two.xlsx
1
 
Rob HensonFinance AnalystCommented:
Don't understand the requirement but maybe use of the SMALL function will help.

Syntax =SMALL(Range,k)

Range - the range of cells containing the values
k - the position required, 1 = smallest, 2 = next smallest.

For your sample
=SMALL(B1:B36,1)  gives smallest value, result = 2
=SMALL(B1:B36,2)  gives next smallest value, result also = 2

Thanks
Rob
1
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hi Ian,

You may try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

In C2
=AND(SMALL(IF($A$2:$A$36=A2,$B$2:$B$36),1)<=3.5,(SMALL(IF($A$2:$A$36=A2,$B$2:$B$36),2)-SMALL(IF($A$2:$A$36=A2,$B$2:$B$36),1))>=2)*1

Open in new window

and then copy it down.

In the attached, I have placed the formula in column D for your reference.
Valuesgreaterequal-to-two.xlsx
2
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
raceproretiredAuthor Commented:
Hi Rob,

Not sure I can explain it any better than the description I gave on the spreadsheet.
If the lowest number in a range is say 2 and the second lowest number in the same range
is 5 then the difference = 3. My stipulation is the difference should be => 2. In which case
this example would qualify. If there are multiple lowest numbers then those lowest numbers should
not exceed 3.5 each otherwise does not qualify.
0
 
Saqib Husain, SyedEngineerCommented:
How do you define the end of the range? Is it the first occurrence of your criteria?
1
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Ian

You haven't responded back yet. Did you test the formula I suggested?
1
 
raceproretiredAuthor Commented:
I had to run the program overnight to test it on a large file.
I'm now looking at the results. So far I can see that it disqualifies
all situations whereby there are joint lowest numbers. I wanted to
keep those that are less than 3.5.
example.
index    value  
123         12
123         16
123            3
123          18
123            3
Cheers
0
 
raceproretiredAuthor Commented:
Hi Saqib,
I think Neeraj has almost succeeded. But as always thank you for your interest.
The end of the range is the last index number.
Thanks
Ian
0
 
raceproretiredAuthor Commented:
You must be psychic. I was only just thinking yesterday what a scary looking avatar you had chosen. Now replaced with an Omar Sharif lookalike. Handsome dude :)
Your modified formula works a treat
Thanks again Neeraj
Ian
1
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Ian! Glad it worked as desired.

Lol... I am sorry that you found my previous avatar scary. Glad you liked the latest avatar and thanks for the compliment. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.