Use Excel to evaluate if two events happened back-to-back out of a series of events - Stack Overflow-软件玩家

admin管理员组
文章数量:1357639

I'm trying to use Excel to return TRUE if the time difference between two specific events (Event A and Event B) is smaller than the difference between Event B and all other events (which there are 20+ of).

At first this seemed simple enough, as I took the absolute value of the difference between Event A and Event B and compared it to the absolute value of the difference between Event B and every other event to find if A and B were closer together than all the rest.

However, the kicker is that I don't want any events that happened later than Event B to be considered. My spreadsheet looks like this:

I've included my formula below. It's comparing the difference between the events in columns AX and P to see if they're smaller than the difference between columns AX and ____. How can I adjust it so that dates great than that in column AX are not considered?

=IF(AND(
ABS(AX14273-P14273)<ABS(AX14273-H14273),
ABS(AX14273-P14273)<ABS(AX14273-I14273),
ABS(AX14273-P14273)<ABS(AX14273-J14273),
ABS(AX14273-P14273)<ABS(AX14273-K14273),
ABS(AX14273-P14273)<ABS(AX14273-L14273),
ABS(AX14273-P14273)<ABS(AX14273-M14273),
ABS(AX14273-P14273)<ABS(AX14273-N14273),
ABS(AX14273-P14273)<ABS(AX14273-O14273),
ABS(AX14273-P14273)<ABS(AX14273-Q14273),
ABS(AX14273-P14273)<ABS(AX14273-R14273),
ABS(AX14273-P14273)<ABS(AX14273-S14273),
ABS(AX14273-P14273)<ABS(AX14273-T14273),
ABS(AX14273-P14273)<ABS(AX14273-U14273),
ABS(AX14273-P14273)<ABS(AX14273-V14273),
ABS(AX14273-P14273)<ABS(AX14273-W14273),
ABS(AX14273-P14273)<ABS(AX14273-X14273),
ABS(AX14273-P14273)<ABS(AX14273-Y14273),
ABS(AX14273-P14273)<ABS(AX14273-Z14273),
ABS(AX14273-P14273)<ABS(AX14273-AA14273),
ABS(AX14273-P14273)<ABS(AX14273-AB14273),
ABS(AX14273-P14273)<ABS(AX14273-AC14273),
ABS(AX14273-P14273)<ABS(AX14273-AD14273),
ABS(AX14273-P14273)<ABS(AX14273-AE14273),
ABS(AX14273-P14273)<ABS(AX14273-AF14273),
ABS(AX14273-P14273)<ABS(AX14273-AG14273),
ABS(AX14273-P14273)<ABS(AX14273-AH14273),
ABS(AX14273-P14273)<ABS(AX14273-AI14273),
ABS(AX14273-P14273)<ABS(AX14273-AJ14273),
ABS(AX14273-P14273)<ABS(AX14273-AK14273),
ABS(AX14273-P14273)<ABS(AX14273-AL14273),
ABS(AX14273-P14273)<ABS(AX14273-AM14273),
ABS(AX14273-P14273)<ABS(AX14273-AN14273),
ABS(AX14273-P14273)<ABS(AX14273-AO14273),
ABS(AX14273-P14273)<ABS(AX14273-AP14273),
ABS(AX14273-P14273)<ABS(AX14273-AQ14273),
ABS(AX14273-P14273)<ABS(AX14273-AR14273),
ABS(AX14273-P14273)<ABS(AX14273-AS14273),
ABS(AX14273-P14273)<ABS(AX14273-AT14273),
ABS(AX14273-P14273)<ABS(AX14273-AU14273),
ABS(AX14273-P14273)<ABS(AX14273-AV14273),
ABS(AX14273-P14273)<ABS(AX14273-AW14273),
ABS(AX14273-P14273)<ABS(AX14273-AY14273),
ABS(AX14273-P14273)<ABS(AX14273-AZ14273),
ABS(AX14273-P14273)<ABS(AX14273-BA14273),
ABS(AX14273-P14273)<ABS(AX14273-BB14273),
ABS(AX14273-P14273)<ABS(AX14273-BC14273),
ABS(AX14273-P14273)<ABS(AX14273-BD14273),
ABS(AX14273-P14273)<ABS(AX14273-BE14273),
ABS(AX14273-P14273)<ABS(AX14273-BF14273),
ABS(AX14273-P14273)<ABS(AX14273-BG14273),
ABS(AX14273-P14273)<ABS(AX14273-BH14273),
ABS(AX14273-P14273)<ABS(AX14273-BI14273),
ABS(AX14273-P14273)<ABS(AX14273-BJ14273),
ABS(AX14273-P14273)<ABS(AX14273-BK14273),
ABS(AX14273-P14273)<ABS(AX14273-BL14273),
ABS(AX14273-P14273)<ABS(AX14273-BM14273),
ABS(AX14273-P14273)<ABS(AX14273-BN14273),
ABS(AX14273-P14273)<ABS(AX14273-BO14273),
ABS(AX14273-P14273)<ABS(AX14273-BP14273),
ABS(AX14273-P14273)<ABS(AX14273-BQ14273),
ABS(AX14273-P14273)<ABS(AX14273-BR14273),
ABS(AX14273-P14273)<ABS(AX14273-BS14273),
ABS(AX14273-P14273)<ABS(AX14273-BT14273),
ABS(AX14273-P14273)<ABS(AX14273-BU14273),
ABS(AX14273-P14273)<ABS(AX14273-BV14273),
ABS(AX14273-P14273)<ABS(AX14273-BW14273),
ABS(AX14273-P14273)<ABS(AX14273-BX14273),
ABS(AX14273-P14273)<ABS(AX14273-BY14273),
ABS(AX14273-P14273)<ABS(AX14273-BZ14273),
ABS(AX14273-P14273)<ABS(AX14273-CA14273),
ABS(AX14273-P14273)<ABS(AX14273-CB14273),
ABS(AX14273-P14273)<ABS(AX14273-CC14273),
ABS(AX14273-P14273)<ABS(AX14273-CD14273),
ABS(AX14273-P14273)<ABS(AX14273-CE14273),
ABS(AX14273-P14273)<ABS(AX14273-CF14273),
ABS(AX14273-P14273)<ABS(AX14273-CG14273),
ABS(AX14273-P14273)<ABS(AX14273-CH14273)),"Yes","No")

However, the kicker is that I don't want any events that happened later than Event B to be considered. My spreadsheet looks like this:

=IF(AND(
ABS(AX14273-P14273)<ABS(AX14273-H14273),
ABS(AX14273-P14273)<ABS(AX14273-I14273),
ABS(AX14273-P14273)<ABS(AX14273-J14273),
ABS(AX14273-P14273)<ABS(AX14273-K14273),
ABS(AX14273-P14273)<ABS(AX14273-L14273),
ABS(AX14273-P14273)<ABS(AX14273-M14273),
ABS(AX14273-P14273)<ABS(AX14273-N14273),
ABS(AX14273-P14273)<ABS(AX14273-O14273),
ABS(AX14273-P14273)<ABS(AX14273-Q14273),
ABS(AX14273-P14273)<ABS(AX14273-R14273),
ABS(AX14273-P14273)<ABS(AX14273-S14273),
ABS(AX14273-P14273)<ABS(AX14273-T14273),
ABS(AX14273-P14273)<ABS(AX14273-U14273),
ABS(AX14273-P14273)<ABS(AX14273-V14273),
ABS(AX14273-P14273)<ABS(AX14273-W14273),
ABS(AX14273-P14273)<ABS(AX14273-X14273),
ABS(AX14273-P14273)<ABS(AX14273-Y14273),
ABS(AX14273-P14273)<ABS(AX14273-Z14273),
ABS(AX14273-P14273)<ABS(AX14273-AA14273),
ABS(AX14273-P14273)<ABS(AX14273-AB14273),
ABS(AX14273-P14273)<ABS(AX14273-AC14273),
ABS(AX14273-P14273)<ABS(AX14273-AD14273),
ABS(AX14273-P14273)<ABS(AX14273-AE14273),
ABS(AX14273-P14273)<ABS(AX14273-AF14273),
ABS(AX14273-P14273)<ABS(AX14273-AG14273),
ABS(AX14273-P14273)<ABS(AX14273-AH14273),
ABS(AX14273-P14273)<ABS(AX14273-AI14273),
ABS(AX14273-P14273)<ABS(AX14273-AJ14273),
ABS(AX14273-P14273)<ABS(AX14273-AK14273),
ABS(AX14273-P14273)<ABS(AX14273-AL14273),
ABS(AX14273-P14273)<ABS(AX14273-AM14273),
ABS(AX14273-P14273)<ABS(AX14273-AN14273),
ABS(AX14273-P14273)<ABS(AX14273-AO14273),
ABS(AX14273-P14273)<ABS(AX14273-AP14273),
ABS(AX14273-P14273)<ABS(AX14273-AQ14273),
ABS(AX14273-P14273)<ABS(AX14273-AR14273),
ABS(AX14273-P14273)<ABS(AX14273-AS14273),
ABS(AX14273-P14273)<ABS(AX14273-AT14273),
ABS(AX14273-P14273)<ABS(AX14273-AU14273),
ABS(AX14273-P14273)<ABS(AX14273-AV14273),
ABS(AX14273-P14273)<ABS(AX14273-AW14273),
ABS(AX14273-P14273)<ABS(AX14273-AY14273),
ABS(AX14273-P14273)<ABS(AX14273-AZ14273),
ABS(AX14273-P14273)<ABS(AX14273-BA14273),
ABS(AX14273-P14273)<ABS(AX14273-BB14273),
ABS(AX14273-P14273)<ABS(AX14273-BC14273),
ABS(AX14273-P14273)<ABS(AX14273-BD14273),
ABS(AX14273-P14273)<ABS(AX14273-BE14273),
ABS(AX14273-P14273)<ABS(AX14273-BF14273),
ABS(AX14273-P14273)<ABS(AX14273-BG14273),
ABS(AX14273-P14273)<ABS(AX14273-BH14273),
ABS(AX14273-P14273)<ABS(AX14273-BI14273),
ABS(AX14273-P14273)<ABS(AX14273-BJ14273),
ABS(AX14273-P14273)<ABS(AX14273-BK14273),
ABS(AX14273-P14273)<ABS(AX14273-BL14273),
ABS(AX14273-P14273)<ABS(AX14273-BM14273),
ABS(AX14273-P14273)<ABS(AX14273-BN14273),
ABS(AX14273-P14273)<ABS(AX14273-BO14273),
ABS(AX14273-P14273)<ABS(AX14273-BP14273),
ABS(AX14273-P14273)<ABS(AX14273-BQ14273),
ABS(AX14273-P14273)<ABS(AX14273-BR14273),
ABS(AX14273-P14273)<ABS(AX14273-BS14273),
ABS(AX14273-P14273)<ABS(AX14273-BT14273),
ABS(AX14273-P14273)<ABS(AX14273-BU14273),
ABS(AX14273-P14273)<ABS(AX14273-BV14273),
ABS(AX14273-P14273)<ABS(AX14273-BW14273),
ABS(AX14273-P14273)<ABS(AX14273-BX14273),
ABS(AX14273-P14273)<ABS(AX14273-BY14273),
ABS(AX14273-P14273)<ABS(AX14273-BZ14273),
ABS(AX14273-P14273)<ABS(AX14273-CA14273),
ABS(AX14273-P14273)<ABS(AX14273-CB14273),
ABS(AX14273-P14273)<ABS(AX14273-CC14273),
ABS(AX14273-P14273)<ABS(AX14273-CD14273),
ABS(AX14273-P14273)<ABS(AX14273-CE14273),
ABS(AX14273-P14273)<ABS(AX14273-CF14273),
ABS(AX14273-P14273)<ABS(AX14273-CG14273),
ABS(AX14273-P14273)<ABS(AX14273-CH14273)),"Yes","No")

Share Improve this question edited Mar 31 at 10:09 Mark Rotteveel 110k229 gold badges156 silver badges224 bronze badges asked Mar 27 at 16:34 user30082921 12 bronze badges

1 Your example shows some rows, none of which have anything recorded for Event B. What does all this talk about Event B relationships mean when there's nothing to relate it to? – Chris Maurer Commented Mar 27 at 17:18
Hey Chris, that's my bad, I chose a confusing screenshot. Just updated it to better reflect the fact that there should be data for some Event B rows. – user30082921 Commented Mar 27 at 22:26
Why not provide some sample data we can use to create your scenario? – tinazmu Commented Mar 27 at 23:19
"adjust it so that dates great than that in column AX are not considered?" d u mean.. it will not be considered for "difference between column P & AX" evaluation... Or d u mean =IF(AX14273<MAX(MAX(H14273:O14273),MAX(Q14273:AW14273,MAX(AY14273:CH14273)),"Yes","No") ? – p._phidot_ Commented Mar 28 at 8:30
@p._phidot_ I'm thinking moreso it won't be considered for the "difference between column P & AX evaluation." Here's an example: Event A = 2/4/25, Event B = 2/7/25, Event C = 2/3/25, and Event D = 2/8/25. Of these events, I want to see whether the difference between A and B is smaller than the difference between B and any other events ONLY IF B occurs before the other events. So I wouldn't want my equation to even evaluate the difference between B and D, since D occurs at a later date. – user30082921 Commented Mar 28 at 16:25

| Show 4 more comments

1 Answer 1

Sorted by: Reset to default 0

Following the example code and comment given by OP, event A, B, C, D is referred.

A is P14273
B is AX14273

C is H14273:O14273
D is Q14273:AW14273
E is AY14273:CH14273 (I add this for clearer example)

[ Condition ]

(in sequence of priority..)

IF any value in C, D, E is later or the same day as B. Then, it will not be evaluated
IF C, D, E date is before B. Then, It will be evaluated

IF abs(A-B)< abs(B-C) , for all C. Then, Cond1 is TRUE
IF abs(A-B)< abs(B-D) , for all D. Then, Cond2 is TRUE
IF abs(A-B)< abs(B-E) , for all E. Then, Cond3 is TRUE

IF and(Cond1,Cond2,Cond3) is TRUE. Then, Output is "Yes"

Else, Output is "No"

IF C, D, E date is before B Then, Output is "" (nothing) <-- (I add this for clearer example)

[ Execution ]

Example for considering only A,B,C :

=IF(MIN(H14273:O14273)\>=AX14273,"",  
    IF(ABS(P14273 - AX14273)   
       \< MAX(   
         ABS( AX14273 - IF(H14273:O14273\>=AX14273,AX14273,H14273:O14273) )  
       ),  
    "Yes" , "No")  
  )

considering all A-E :

=IF(MIN(H14273:O14273)\>=AX14273,"",  
   IF(ABS(P14273 - AX14273)  
      \< MAX(  
         ABS( AX14273 - IF(H14273:O14273\>=AX14273,AX14273,H14273:O14273),  
         ABS( AX14273 - IF(Q14273:AW14273\>=AX14273,AX14273,Q14273:AW14273),  
         ABS( AX14273 - IF(AY14273:CH14273\>=AX14273,AX14273,AY14273:CH14273) )  
      ),  
   "Yes" , "No")  
 )

[ Notes ]

=IF(H14273:O14273>=AX14273,AX14273,H14273:O14273)

is an array formula, so if it is typed into a cell.. it will spill to the right.

ABS(AX14273-IF(H14273:O14273>=AX14273,AX14273,H14273:O14273)

will '0' the dates later than AX14273, then find the maximum date difference in the range.

Please share if it works/not/understandable. \(^_^)/

本文标签： Use Excel to evaluate if two events happened backtoback out of a series of eventsStack Overflow

版权声明：本文标题：Use Excel to evaluate if two events happened back-to-back out of a series of events - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人，转载请联系作者并注明出处：http://www.betaflare.com/web/1744079483a2587363.html，本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。

编程频道|软件玩家 - 软件改变生活！

Use Excel to evaluate if two events happened back-to-back out of a series of events - Stack Overflow

1 Answer 1

更多相关文章