问题描述
当前，我有下表
公司---------日期--------交换----- -大小
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％）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！