Finding the Lowest Risk% with the Highest Return% while ensuring each subsequent record has a higher return% and risk% than the previous record

Question

 I have a table of Stocks with Return% and Risk%, how do I find the lowest risk percentage with the highest return percentage while ensuring that each subsequent record has a higher return% and risk% than the previous record?

Steps:

1) To first find the Lowest Risk% with the Highest Return% 

2) then find the next Lowest Risk% that is greater or equal to the previous Risk% record and

3) the Return% is also greater or equal to the previous Return%

Below is the data in Excel I need the solution for:

Company	Return%	Risk%
ABBV	23.76%	5.05%
CVX	16.14%	4.45%
DG	20.91%	4.26%
FDS	20.36%	5.77%
JNJ	17.10%	3.93%
LLY	16.63%	4.99%
LMT	15.05%	5.27%
LOW	18.90%	6.34%
LVMUY	19.00%	6.73%
MA	30.85%	5.15%
MSFT	26.27%	5.69%
NDAQ	15.88%	6.32%
PEP	17.43%	4.09%
PFE	16.24%	4.81%
RTX	14.12%	5.31%
SPY	11.22%	3.50%
T	17.94%	4.64%
V	19.04%	4.38%
VZ	16.00%	4.41%
XOM	15.43%	4.01%

Column A: Company

Column B: Return%

Column C: Risk%

The Result output should look like below:

Company	Return%	Risk%
SPY	11.22%	3.50%
JNJ	17.10%	3.93%
PEP	17.43%	4.09%
DG	20.91%	4.26%
ABBV	23.76%	5.05%
MA	30.85%	5.15%

Answer 1

Hi Albert,

I've answered your question with filters and with formulas.

Below please find the formulas used in my answers.

(These formulas' cell-references would change for you depending upon where your data set 'lives'.)

Please feel free to message me with any additional questions.

=SORTBY(A3:C22,C3:C22)

=IF(J3=MAX($J$3:J3),1,0)

=FILTER(I3#,L3:L22,1)

Thank you,

Jason

Answer 2

Hi Albert,

The result must be in Risk order. I think you want a subset of the original list, such that the Risks are in increasing order, and each Reward is greater than all previous Rewards in the original list

1. Add a label in D1-“current high reward”
2. Sort the table by Risk (ascending) and next level Return (ascending). This guarantees the rows are in increasing order of Risk. They are not necessarily in increasing order of Reward.
3. D2 = b2. The Reward for the first record.
4. D3 =IF(B3>B2, B3, D2), spread over rest of column D. This ensures that each D has the largest Reward from the beginning of the list.
5. E2 = True. E is true for each record whose Reward is the largest since the beginning of the list
6. E3=IF(D3>D2,True,False), spread over rest of column E. This ensures that each row with E equals True has Reward > any earlier Rewards.
7. Filter on E = True contains the subset that satisfies the two constraints: 
8. —Risk >previous Risks and 
9. —Reward > previous Rewards.