logical operations in an Array formula

[SUPPO_USN]

I have the below Array formula that cycles through all data points in the 'Data with Outliers' worksheet where it matches Customer and Product and Destination between Date 1 and Date 2 and returns the average number of days held in the 'Hold Time Analysis - RDD' worksheet. This formula works as intended.

Please Login or Register  to view this content.

Hold Time Analysis - RDD Column A: Customer
Hold Time Analysis - RDD Column B: Product
Hold Time Analysis - RDD Column C: Destination
$T$3: Date1
$V$3: Date2
Data with Outliers Column O: Days held before returning

I am attempting to modify the above formula to meet a new requirement. I need to calculate the number of days held starting from the date of Actual receipt or their Requested Delivery Date (RDD) Whichever is later.

I have the dates they actually received the product (Data with Outliers Column G) and their RDD (Data with Outliers Column H).

My thoughts are that I need an IF() statement in the 'Data with Outliers'!$O$5:$O$1053 section. I tried this:

Please Login or Register  to view this content.

Data with Outliers Column I: Difference between the RDD and Actual Receipt.

but it didn't work so i'm assuming there's something I need to do different since it's an array formula. similar to using * instead of AND in the initial formula. That, or it's not possible to do what I want.

Any guidance is appreciated.

r/

SUPPO

[Kaper]

how you understand

Please Login or Register  to view this content.

because on the left side you have 5 cells and on right 1000+

for instance if for each H5:H1053 you look if smallest of g56:G60 is (equivalent to all 5 are) bigger than it try:

Please Login or Register  to view this content.

of course for largest comparision use MAX (eqivalent to if any of G56:G60 is bigger than Hx

[SUPPO_USN]

Thanks Kaper for the assistance. I somehow missed that the data ranges weren't the same size (they were supposed to be) and that solved my initial problem. I modified my code to the below and it is partially working:

Please Login or Register  to view this content.

The issue I'm coming across now is, I can't drag the formula down to fill the cells below it. I get wonky results if I do. I can't copy and paste the cell either as I get the same result. If I double click on the cell, copy the formula, double click on the destination cell, paste the formula, and modify the references ($A5, $B5, $C5 to $A6, $B6, $C6, etc), it works. Any ideas on how to copy the data down? When I do drag the formula down, the references adjust correctly, I just don't get the correct result. Luckily in this application there are only 12 rows of data, but for future applications it might not be as feasible...

I also apologize for taking a while to respond. I'd generated a clunky workaround (added columns, multiple formulas, etc) in the mean time. Thanks again for the assistance thus far and any input on the copy/drag issue is appreciated.

r/

SUPPO

[Kaper]

You are welcome
and thanks for the reputation