I am trying to devise an excel formula that is able to find the maximum value within a range of values that is less than a certain threshold. For example, if there was a range of values 1, 2, 3, 4 and 5, and the threshold value was set to 4, then the formula would return 3 as this is the maximum value below this threshold. If the threshold was set to 4.1 the formula would then return 4.
Many thanks in advance for your help.
Last edited by Fishhooky; 09-16-2010 at 08:55 AM.
Try this
=LARGE(B1:B10,(RANK(D1,B1:B10)+1))
where the value that you set as maximum in D1
Just realised if the value isn't in the list it returns N/A - there will be another solution
edvwvw
Try this:
=MAX(IF(B1:B5<D1,B1:B5))
Comfirmed with ctrl+shift+ente
Similar to the earlier ex.
=SMALL(B1:B10,1+COUNTIF(B1:B10,"<"&D1))
where D1 holds threshold
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thanks for your replies, both edvwvw and Donkeyote's solutions seem to work for my case.
Thanks again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks