# 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.
Thanks
Ian
Valuesgreaterequal-to-two.xlsx
###### 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.

Finance 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.

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

Thanks
Rob
1
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
``````
and then copy it down.

In the attached, I have placed the formula in column D for your reference.
Valuesgreaterequal-to-two.xlsx
2
retiredAuthor 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
EngineerCommented:
How do you define the end of the range? Is it the first occurrence of your criteria?
1
Excel & VBA ExpertCommented:
@Ian

You haven't responded back yet. Did you test the formula I suggested?
1
retiredAuthor 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
retiredAuthor 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
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
``````
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.

retiredAuthor 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
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.