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]
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]