average days based on matching data in different rows

[vern07]

I have a report of over 2000 lines, what is attached is just a sample. I want a formula that finds matching PO and Line numbers and calculates the average days between the "RECEIVE" and "DELIVER" dates. In the sample I color coded the matching RECEIVE and DELIVER POs and Line numbers, calculated the days in between and then display the average. In the attachment I did this manually to show what I want, but obviously I can't do that for over 2000 lines.

[XOR LX]

Hi,

Can you just clarify the blue lines in your example? The two Receive and Deliver entries for this colour appear to be exact duplicates.

Ignoring those duplicates, I would use, array formula**:

=SUM({1,-1}*SUMIFS(C2:C27,B2:B27,{"DELIVER","RECEIVE"},M2:M27,IF(B2:B27="DELIVER",M2:M27),N2:N27,IF(B2:B27="DELIVER",N2:N27)))/COUNTIF(B2:B27,"DELIVER")

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[Bo_Ry]

Please try

=AVERAGE(IF(B2:B27="deliver",MMULT(SUMIFS(C2:C27,M2:M27,M2:M27,N2:N27,N2:N27,B2:B27,{"DELIVER","RECEIVE"}),{1;-1})/COUNTIFS(M2:M27,M2:M27,N2:N27,N2:N27,B2:B27,"DELIVER")))

Confirm by Ctrl+Shift+Enter

[vern07]

the duplicates are two separate receipts. The PO was for 4, 2 being for job A and 2 for job B. It is also possible to receive partial shipment of the same line number on different days, ex. PO is for 7, the vendor ships 2 on 5/5/20, 3 on 5/10/20 and 2 on the 12th. so each one would need counted as a separate receipt. Does this change things or will this still work?

[vern07]

since my spreadsheet has over 2000 lines and about 500 will be added every week I thought I could just change all the ranges in you formula from X2:X27 to X:X but that returned an average day of 0, any idea why it didn't work?

[XOR LX]

Don't use entire column references! Choose a suitable upper bound, though keep it as small as possible. Alternatively, format your source data as an Excel table.

Updated version, array formula**:

=SUM(MMULT(IF(IF(COUNTIFS(M2:M27,M2:M27,N2:N27,N2:N27,B2:B27,"DELIVER"),B2:B27)={"RECEIVE","DELIVER"},C2:C27,0),{-1;1}))/COUNTIF(B2:B27,"DELIVER")

Regards

[vern07]

Ok, I entered both formulas, 1 on sheet 1 and 1 on the metrics sheet, changing the parameters to include all 35299 lines. Both returned a 0 which I don't understand because it worked for the sample size. I tried to attach the real spreadsheet but I guess 35299 lines is too big. On another note, when I open the file back up the average formula displayed 3841.456, which can't be right. any ideas??

[JeteMc]

If the formula is in cell N35 when the range is changed from 27 to 35299 then it sets up a circular reference.
If the formula is placed in a cell that is not in columns referenced in the formula (i.e. cell T9) then, after the range is changed, it still yields 4.875.
Let us know if you have any questions.

[vern07]

I noticed that so I moved it to N35230, still have a value of 0 when I first hit CTRL+ SHIFT + ENTER, but if I close and save when I open it back up it shows 3897.2412. Here is the formula I have typed in:
{=AVERAGE(IF(B2:B35229="DELIVER",MMULT(SUMIFS(C2:C35229,M2:M35229,M2:M35229,N2:N35229,N2:N35229,B2:B35229,{"DELIVER","RECEIVE"}),{1;-1})/COUNTIFS(M2:M35229,M2:M35229,N2:N35229,N2:N35229,B2:B35229,"DELIVER")))}
I have this typed on another sheet:
{=SUM(MMULT(IF(IF(COUNTIFS(Sheet1!M2:M35229,Sheet1!M2:M35229,Sheet1!N2:N35229,Sheet1!N2:N35229,Sheet1!B2:B35229,"DELIVER"),Sheet1!B2:B35229)={"RECEIVE","DELIVER"},Sheet1!C2:C35229,0),{-1;1}))/COUNTIF(Sheet1!B2:B35229,"DELIVER")}

[JeteMc]

Although it takes a while to calculate, both formulas eventually display 4.875
See the orange shaded cells U9 on Sheet1 and A1 on Sheet2.
Let us know if you have any questions.

[vern07]

I know the formula works in the sample spreadsheet, I can't figure out why it doesn't work in the real thing., I know the answer it displays of over 3500 days is wrong.

[JeteMc]

It would possibly be easier to troubleshoot the problem if we could see the file.
As the file with the first 26 rows is only 13.8 KB perhaps you could upload the actual file. If the file is too large to upload as an .xlsx, then there are some contributors that may be willing to open a .zip which may be up to 9.77 MB.

[vern07]

tried to upload the zip but I keep getting a upload error.

[JeteMc]

What is the size of the .zip file?

[vern07]

2.55 MB, it should work

[JeteMc]

Have you attempted to upload the .zip file again?
If it still will not upload then perhaps copy only the columns involved in the formula over to a sheet in another workbook.
If that produces a file of over 1000 KB then remove just enough rows so that the file size is smaller than 1000 KB (hopefully that will be enough rows that the problem will replicate).
Let us know if you have any questions.

[vern07]

good idea lets try it, success

[XOR LX]

When you amend the end row reference in my formula accordingly, it gives exactly the same answer as Bo_Ry's. Perhaps you should tell us what the answer should be, and why you think our formulas are giving incorrect results. This is not sufficient for us to go on:

I know the answer it displays of over 3500 days is wrong.

Regards

[vern07]

First, I apologize for the late response, I was out of town. The answer that the formula produces in O2 is 3835.11 days, since all the dates are within 2020 that can't be the average days from the time something is received till the time it is delivered.

[XOR LX]

You're correct. The problem is with the multiple entries per PO/LINE. 405171/1, for example, has 33 entries in there, but this is split into 21 with status DELIVER yet only 12 with status RECEIVE. For any formula solution to work it would be necessary to know how to 'match' the 12 RECEIVE entries with their corresponding DELIVER entries (and so reject the remaining 9 superfluous DELIVER entries.

I suggest you add an additional column of identifiers into the worksheet such that DELIVER/RECEIVE pairings are correctly identified. Without that, I don't see how this can be feasibly solved.

Regards

[vern07]

thank you, what would the formula be for the identifiers

[XOR LX]

I'm not sure what the formula would be, since, for a given order, I genuinely don't know which of the various multiple RECEIVE entries is associated with a given DELIVER entry. This is only something your company would know: as I said, for example, given 21 entries with status DELIVER yet only 12 with status RECEIVE, how do I know which of those 21 DELIVER were received or not? How can we calculate the average time between delivery and reception if we can't match each DELIVER with its associated RECEIVE?

Regards