# Formula help: Calculating Averages for Rows

1. ## Formula help: Calculating Averages for Rows

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.

2. ## Re: Formula help: Calculating Averages for Rows

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.

3. ## Re: Formula help: Calculating Averages for Rows

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?

4. ## Re: Formula help: Calculating Averages for Rows

Try then:

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

5. ## Re: Formula help: Calculating Averages for Rows

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.

6. ## Re: Formula help: Calculating Averages for Rows

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

7. ## Re: Formula help: Calculating Averages for Rows

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.

8. ## Re: Formula help: Calculating Averages for Rows

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....

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1