The k-th smallest elment of a sub list defined dynamically form a super set based on 2 criteria

adnane

New Member
Joined
Oct 17, 2014
Messages
1
Hello,

I'm trying to solve the below problem using only Excel:

-Column A contains dates
-Column B contains scenarios number
-Column C contains object
-Column D contains a risk measure for this particular Object for a given date and scenario

My challenge here is to find the k-th smallest risk measure (column D) for a given Object and Date using only Excel and ideally with only one formula (without using too many helpers functions).

Obviousely, here we need first to find the sub list of possible risk measure based on the given object and date, then we calculate the k-th smallest elements of this sub list.

I was hoping to solve this using some previous threads about subarray formulas in excel (using index, row, etc) but no clue.

Many thanks in advance for your assitance with this matter.

ad.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
The following array-formula will do what you are wanting.

=SMALL(IF($D$2:$D$500*($C$2:$C$500=$I$3)*($A$2:$A$500=$I$2)<>0,$D$2:$D$500*($C$2:$C$500=$I$3)*($A$2:$A$500=$I$2),""),$I$4)
This formula will need to be confirmed with CTRL+SHIFT+ENTER after entering it in the cell and you will see curly braces {} surrounding the formula that Excel adds if done correctly.

NOTE: I am assuming your values start in row 2 (with row 1 as headers) You can edit the $x$2:$x$500 ranges to however many rows of data you have.
Also, the cells to lookup are defined as
$I$2: Lookup Date
$I$3: Object
$I$4: k
 
Upvote 0

Forum statistics

Threads
1,226,730
Messages
6,192,708
Members
453,748
Latest member
akhtarf3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top