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
Ian BellretiredAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ian BellretiredAuthor 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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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
Ian BellretiredAuthor 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
Ian BellretiredAuthor 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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ian BellretiredAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.