Multi Criterion Max If Statement [英] Multi Criterion Max If Statement

问题描述

我的数据集看起来像这样......

My dataset looks like this...

State   Close Date  Probability      Highest Prob/State
WA       12/31/2016   50%            FALSE
WA       12/19/2016   80%            FALSE
WA       10/15/2016   80%            TRUE

我的目标是建立一个公式来填充最右边的列。该公式应评估每个州内的关闭日期和概率。首先，它应该选择最高概率，然后如果存在概率上的关系，则应该选择最近的关闭日期（如示例中所示）。对于该记录，它应显示为TRUE。

My objective is to build a formula to populate the right-most column. The formula should assess Close Dates and Probabilities within each state. First, it should select the highest probability, then it should select the nearest close date if there is a tie on probability (as in the example). For that record, it should read "TRUE".

我认为这将包括MAX IF语句，但无法使其工作。

I assume this would include a MAX IF statement but haven't been able to get it to work.

这是我正在使用的一组更强大的数据。实际上可能更容易首先找到每个区域内的最高概率，然后选择最小（最旧）日期，如果存在概率上的平局。这也符合我的目的。

Here is a more robust set of data I'm working with. It may actually be easier to first find the highest probability within each Region then select the minimum (oldest) date if there is a tie on probability. This too will serve my purposes.

Region                    Forecast Close Date   Probability (%)
Okeechobee FL             6/27/2016                 90
Okeechobee West FL        7/1/2016                  40
Albany GA                 3/11/2016                 100
Emerald Coast FL          6/30/2016                 60
Emerald Coast FL          10/1/2016                 40
Cullman_Hartselle TN      4/30/2016                 10
North MS                  10/1/2016                 25
Roanoke VA                8/31/2016                 25
Roanoke VA                8/1/2016                  40
Gardena CA                6/1/2016                  80
Gardena CA                6/1/2016                  80
Lomita-Harbor City        6/30/2016                 60
Lomita-Harbor City        6/30/2016                 0
Lomita-Harbor City        6/30/2016                 40
Eastern NC                6/30/2016                 60
Northwest NC              9/16/2016                 10
Fort Collins_Greeley CO   3/1/2016                  100
Northwest OK              6/30/2016                 100
Southwest MO              7/29/2016                 90
Northern NH-VT            3/1/2016                  20
South DE                  12/1/2016                 0
South DE                  12/1/2016                 20
Kingston NY               12/30/2016                5
Longview WA               11/30/2016                5
North DE                  12/1/2016                 20
North DE                  12/1/2016                 0
Salt Lake City UT         8/31/2016                 20
Idaho Panhandle           8/26/2016                 0
Bridgeton_Salem NJ        7/1/2016                  25
Bridgeton_Salem NJ        7/1/2016                  65
Layton_Ogden UT           3/25/2016                 5
Central OR                6/30/2016                 10

推荐答案

以下数组公式应该工作：

The following Array formula should work:

=(ABS(B2-$F$2)=MIN(IF(($A$2:$A$33=A2)*(C2=MAX(IF($A$2:$A$33=A2,$C$2:$C$33))),ABS($B$2:$B$33-$F$2))))*(C2=MAX(IF($A$2:$A$33=A2,$C$2:$C$33)))>0

作为数组公式，在退出编辑模式时使用Ctrl-Shift-Enter。如果操作正确，Excel将在公式周围放置 {} 。

Being an array formula use Ctrl-Shift-Enter when exiting Edit mode. If done properly Excel will put {} around the formula.

编辑

Edit

添加了@tigeravatar建议，以避免挥发性功能。

Added @tigeravatar suggestion to avoid volatile functions.

这篇关于Multi Criterion Max If Statement的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！