On-Time Completion Formula | Offset Dynamic Named Ranges in MM/DD/YYYY Format

Column A:A and B:B consist of dates (mm/dd/yyyy) and are both dynamic offset named ranges. Column A:A is the "Planned End Date" and column B:B is the "Actual End Date". I want to count the number of instances where the date in B:B is greater than the date in A:A on the same row, then divide by the total number of rows consisting of a date in column B:B. I write Array functions from my data set because it is in SQL table format residing in Excel, and since my End Users submit new row data on a daily basis using a VB data entry UserForm, the named ranges of each column must be dynamic, thus I offset them to grow with each additional. row of data added.

The output I'm looking for will be a percentage yield.

Any help is greatly appreciated.

Try

=SUMPRODUCT(--(B1:B1000>A1:A1000))/COUNT(B1:B1000)

I've included a sample workbook. Note that I've also offset the named ranges of column A:A and B:B so they are dynamic and can potentially grow as more rows of data are added. The sample data set exists in the first sheet, 'Raw Data', and the desired output is in sheet2, 'Output'.

The formula should equal 25% if done correctly. So it counts the total number of [Actual End Date] that are "<=" [Planned End Date], then divides by the total number of rows in the data set.

THANK YOU TREMENDOUSLY!

Sample Workbook.xlsx

Just take the formula I posted and replace the ranges with your respective 'Named Ranges'
Also need to change the > to <=
Your Original Post said count if B is > A
But your last post changed to count if B <= A

That's the ticket! You nailed it So grateful!!

You're welcome.

Sample Workbook.xlsx

Sample workbook above.

One additional question. Assume that column B:B [Actual End Date] is manually populated. Array formulas can't calculate if the variables are NULL. What if Column A:A [Planned End Date] is populated for three rows (A1:A3), but column B:B [Actual End Date] only has data for rows B1:B2.

I would still like to produce output for the rows that include data within the same row in both column A:A and B:B.

This one is a stumper...

You'll need a different method to get your dynamic named ranges.
Because BOTH ranges MUST be the same dimensions in sumproduct.

Here it is with hard coded ranges.
=SUMPRODUCT(--('Raw Data'!B2:B1000<='Raw Data'!A2:A1000),--('Raw Data'!B2:B1000<>""))/COUNT('Raw Data'!B2:B1000)



To get your named ranges the same dimensions, I'd suggest using the same column for the counta part of both named range formulas.
Probably column A.
And I recommend making both named ranges start in row 2 instead of 1 (to exclude the header rows)

You nailed it again! Thank you so much!

I renamed the COUNTA part of my offset named ranges as you instructed and it works great!

You're welcome.