Assigning values to quartiles for multiple data sets

P

pg101a

New Member
Joined
Jun 22, 2016
Messages
1
Good afternoon,

I'm trying to build an equation that will assign a particular value to a data set depending on if that value is <, between, or > than a certain value. Disregard flows for now, that will be coming from a separate spreadsheet. The remaining five categories are all on one spreadsheet.

For example, the formula would read something like if the alpha for 1yr <50 = 0. If it was between 50 - 75 =.50 and if it was >75 = 1.25. Lather, rinse, repeat for the remaining data points...

Any help on this is greatly appreciated! Thank you

[TABLE="width: 808"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total[/TD]
[TD]% Weighting[/TD]
[/TR]
[TR]
[TD]Flows[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1yr[/TD]
[TD]5[/TD]
[TD]5%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3yr[/TD]
[TD]5[/TD]
[TD]5%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]10%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Performance (Percentile Peers)[/TD]
[TD]1yr (20%)[/TD]
[TD]< 50[/TD]
[TD]50 - 75[/TD]
[TD]> 75[/TD]
[TD]Total[/TD]
[TD]% Weighting[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Alpha[/TD]
[TD]0[/TD]
[TD]0.5[/TD]
[TD]1.25[/TD]
[TD]1.75[/TD]
[TD]1.75%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sharpe[/TD]
[TD]0[/TD]
[TD]0.5[/TD]
[TD]1.25[/TD]
[TD]1.75[/TD]
[TD]1.75%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Peers[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1.75[/TD]
[TD]2.75[/TD]
[TD]2.75%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3yr (45%)[/TD]
[TD]< 50[/TD]
[TD]50 - 75[/TD]
[TD]> 75[/TD]
[TD]Total[/TD]
[TD]% Weighting[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Alpha[/TD]
[TD]0[/TD]
[TD]1.75[/TD]
[TD]2.5[/TD]
[TD]4.25[/TD]
[TD]4.25%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sharpe[/TD]
[TD]0[/TD]
[TD]1.75[/TD]
[TD]2.5[/TD]
[TD]4.25[/TD]
[TD]4.25%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Peers[/TD]
[TD]0[/TD]
[TD]2.75[/TD]
[TD]3[/TD]
[TD]5.75[/TD]
[TD]5.75%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5yr (35%)[/TD]
[TD]< 50[/TD]
[TD]50 - 75[/TD]
[TD]> 75[/TD]
[TD]Total[/TD]
[TD]% Weighting[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Alpha[/TD]
[TD]0[/TD]
[TD]1.25[/TD]
[TD]2[/TD]
[TD]3.25[/TD]
[TD]3.25%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sharpe[/TD]
[TD]0[/TD]
[TD]1.25[/TD]
[TD]2[/TD]
[TD]3.25[/TD]
[TD]3.25%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Peers[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]2.5[/TD]
[TD]4.5[/TD]
[TD]4.50%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]12.75[/TD]
[TD]18.75[/TD]
[TD]31.5[/TD]
[TD]31.50%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]% Weighting[/TD]
[/TR]
[TR]
[TD]Performance (Bench)[/TD]
[TD][/TD]
[TD][/TD]
[TD]1yr (20%)[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]5.00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3yr (45%)[/TD]
[TD]0[/TD]
[TD]12.5[/TD]
[TD]12.50%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5yr (35%)[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]10.00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]27.5[/TD]
[TD]27.50%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total[/TD]
[TD]% Weighting[/TD]
[/TR]
[TR]
[TD]Morningstar[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]*****[/TD]
[TD]0[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]****[/TD]
[TD]0[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]***[/TD]
[TD]0[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]**[/TD]
[TD]2.5[/TD]
[TD]2.50%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]*[/TD]
[TD]5[/TD]
[TD]5.00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7.5[/TD]
[TD]7.50%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total[/TD]
[TD]% Weighting[/TD]
[/TR]
[TR]
[TD]Cost[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Second Quartile[/TD]
[TD]2.5[/TD]
[TD]2.50%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Annual Report Net Exp. Ratio[/TD]
[TD]Third Quartile[/TD]
[TD]5[/TD]
[TD]5.00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Fourth Quartile[/TD]
[TD]7.5[/TD]
[TD]7.50%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15[/TD]
[TD]15%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total[/TD]
[TD]% Weighting[/TD]
[/TR]
[TR]
[TD]Management[/TD]
[TD][/TD]
[TD][/TD]
[TD]PM Stability[/TD]
[TD]< 3yr[/TD]
[TD]1.75[/TD]
[TD]1.75%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]< 1yr[/TD]
[TD]2.5[/TD]
[TD]2.50%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Assets[/TD]
[TD]< $50M[/TD]
[TD]1.75[/TD]
[TD]1.75%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]< $75M[/TD]
[TD]2.5[/TD]
[TD]2.50%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8.5[/TD]
[TD]8.50%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 100.00[/TD]
[TD]100.00%[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
You must log in or register to reply here.

Similar threads

F
  • Solved
AverageIF less than 15 including values under 0 (negative numbers)
Replies
5
Views
465
Fluff
Fluff
K
  • Question Question
Index and Match for date range
Replies
4
Views
572
mmhill
M
H
  • Question Question
Lookup based on multiple criteria and date Range
Replies
2
Views
698
Toadstool
Toadstool
C
  • Solved
Create Function that Calculates a Value Relative to Original Price and Also Constructs a Price Ladder
2
Replies
10
Views
1K
NdNoviceHlp
NdNoviceHlp
J
  • Question Question
VBA to extract row data to sheet and update data set
Replies
0
Views
933
justatroy
J

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

Share this page

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