Formula help: Calculating Averages for Rows

**Tuffnoogies** · 05-11-2011, 10:05 AM

Hello. Brand new to excel here so please excuse if this is a dumb and obvious question. I am trying to come up with a formula that will calculate 2 dates in different rows. For my business, need to come up with something that shows our on time percentages of completed tasks. The Column/row looks something like this:

Due date Completed date

05-11-11 05-11-11
05-12-11 05-14-11
05-10-11 05-10-11
07-26-11 05-31-11
06-01-11 06-15-11

and so on and so forth. I need to make something that calculates the on time percentage. Can anyone help with this please? Thanks for any and all comments.

**NBVC** · 05-11-2011, 11:16 AM

Are you trying to find the average difference in dates?

If so, try:

=SUMPRODUCT(--(A1:A5-B1:B5))/COUNT(A1:A5)

where A1:B5 contain the dates.

**Tuffnoogies** · 05-12-2011, 07:56 AM

Thanks. I would be trying to find the average "on time" percentage.

For my above example, I was on time for 3 of 5, 60%. On time meaning my completed date matches or is before my due date.

So I guess a formula would have to be for how many times my completed date matched or was before my due date. Does that make sense?

**NBVC** · 05-12-2011, 07:59 AM

Try then:

=SUMPRODUCT(--(B2:B6<=A2:A6))/COUNT(A2:A6)

**Tuffnoogies** · 05-12-2011, 08:48 AM

I put it into a row to calculate and it gave me #DIV/0!

=SUMPRODUCT(--(D57:D66<=E57:E66))/COUNT(E57:E66) is what I used. Still trying to get it to work. Thanks for the formula.

**NBVC** · 05-12-2011, 10:17 AM

Does this range not have actual dates: E57:E66 ?

**Tuffnoogies** · 05-12-2011, 10:34 AM

Yes, it has actual dates. The dating convention I'm using is 05-12-11. I tried changing the data to both short date and long date. still gives me that same message.

**NBVC** · 05-12-2011, 11:10 AM

See attached.

Note: I added another condition in case you have blanks in column D.... but it did not error out for me even without that add condition.

Check that the columns are actually formatted as Date... If you change format to General, you should see a 5 digit serial number in place of dates....