索引/匹配如果大于 [英] Index/match if greater than
问题描述
列B具有名称列表,列C具有采取的操作的频率(销售额),而D列具有平均销售价值。
我已经在列G中创建了6个最高销售额的轨迹,这个功能非常有用:
= LARGE($ D $ 8:$ D $ 13,2)
然后我使用列F来确定与每个销售平均值相匹配的名称:
= INDEX($ B $ 8 :$ B $ 13,MATCH(G4,$ D $ 8:$ D $ 13,0))
但是,如果个人销售额超过3,我只想包括销售平均水平。 IE浏览器;列C中的值为> 3。
任何一个可以提供帮助或建议吗?
您可以使用数组公式:
= LARGE(IF(range = criteria,values),n)
/ pre>
所以你的情况:
= LARGE ($ C $ 8:$ C $ 13> 3,$ D $ 8:$ D $ 13),2)
按CTRL + SHIFT + ENTER输入数组公式。
Hoping someone can help me with my trouble with an index/match formula.
Column B has a list of names, column C has the frequency of an action taken (sales made) whilst column D has the average sales value.
I've created in column G a track of the 6 highest sales which has worked great:
=LARGE($D$8:$D$13, 2)
Then I've used column F to determine the name that matches each sales average:
=INDEX($B$8:$B$13, MATCH(G4, $D$8:$D$13, 0))
So far so good! However, I'd like to only include the sales average if that individual has had more than 3 sales. IE; the value in column C is >3.
Can any one provide help or suggestions please?
解决方案You could use an array formula:
=LARGE(IF(range=criteria,values),n)
so for your case:
=LARGE(IF($C$8:$C$13>3,$D$8:$D$13), 2)
press CTRL+SHIFT+ENTER to enter an array formula.
这篇关于索引/匹配如果大于的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!