根据条件对投资组合进行排序（前30％，中40％和下30％） [英] Sorting portfolios based on criteria (top30%,Middle 40%. and Bottom 30%)

问题描述

当前，我有下表

公司---------日期--------交换----- -大小

Company---------Date--------Exchange-------Size

A --------------- 2000 --------- A ----- -------- 50

A---------------2000---------A-------------50

A ------------------- 2001 --------- A ------------ 100

A---------------2001---------A------------ 100

B --------------- 2000 ---- ----- B ------------ 450

B---------------2000---------B------------450

B --------------- 2001 --------- B ------------- 458

B---------------2001---------B------------- 458

我想将每家公司分为三类

I want to allocate each company into three categories

最高 ==>前30％

中 ==>中间40％

底部 ==>底部30％

"Bottom" ==> Bottom 30%

计算临界值时应使用年和交换 = A

Calculating cutoff values should be filtered with 'year' and 'Exchange'=A

我尝试了以下公式

  =if([B/M]>PERCENTILEX.INC(FILTER(June,June[exchg]="A"&&EARLIER([datadate])=[datadate]),June[B/M],0.3),"L",IF([B/M]>PERCENTILEX.INC(FILTER(June,June[exchg]="A"&&EARLIER([datadate])=[datadate]),June[B/M],0.7),"M","T")) 

由于某些原因，它无法正常工作。

For some reasons, It is not working as it should be.

预先感谢。

推荐答案

我已经使用percentilex.inc函数来做到这一点。这是我使用的完整公式。

I have manged to do it with percentilex.inc function. Here is the full formula that I have used.

=SWITCH(TRUE(),Table[SIZE]<=PERCENTILEX.INC(FILTER(Table,EARLIER([Date])=[Date]),Table[SIZE],0.3),"Bottom",Table[SIZE]>=PERCENTILEX.INC(FILTER(Table,EARLIER([Date])=[Date]),Table[SIZE],0.7),"Top","Middle")

这篇关于根据条件对投资组合进行排序（前30％，中40％和下30％）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！