Solved

Excel: Average date difference of a range

Posted on 2016-08-03
10
61 Views
Last Modified: 2016-08-04
Hello,

Seeking help on calculating the average date difference of a range. I thought the best approach would be to use a sumifs, followed by an average however sumifs does't total the numeric date.

See the attached spreadsheet. How would I find the average date difference for dates within a given range?
ee-average-dates.xlsx
0
Comment
Question by:dabug80
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 41741840
Hi,

pls try
=IFERROR(AVERAGEIFS($C$3:$C$9,$C$3:$C$9,">="&B$13,$C$3:$C$9,"<"&B$13+7)-AVERAGEIFS($B$3:$B$9,$C$3:$C$9,">="&B$13,$C$3:$C$9,"<"&B$13+7),"-")

Open in new window

Regards
ee-average-datesV1.xlsx
0
 
LVL 1

Author Comment

by:dabug80
ID: 41741853
Thanks Rgonzo.

I have never heard of Averageifs.

I wish to change the column references to full columns (e.g C:C), doing this doesn't work with averages, so I will need to define the active cell range. Is there an easy way to do this with a growing number of rows?
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 41741862
pls try

=IFERROR(AVERAGEIFS($C:$C;$C:$C;">="&K$13;$C:$C;"<"&K$13+7)-AVERAGEIFS($B:$B;$C:$C;">="&K$13;$C:$C;"<"&K$13+7);"-")

Open in new window

ee-average-datesV2.xlsx
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:xtermie
ID: 41741903
Try the formula in the attached file.  Similar to Rgonzo's but take into account that column C had your data.  Rearranged the data a bit and assumed you wanted to fill in the formula across your dates for each pair of dates (week)
ee-average-dates_wexample.xlsx
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41742588
Alternative, add a duration column to the list with simple formula "Delivery Date" less "Approved Date". You can then do the Averageifs formula on this column.

Assuming Duration in column D, formula in B14 and then copied across:

=IFERROR(AVERAGEIFS($D3:$D9,$C3:$C9,">="&B13,$C3:$C9,"<"&B13+7),0)

You could adjust the duration to only include working days if so required.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41742593
To overcome the expanding range, convert the list to a table; then the above formula will become:

=IFERROR(AVERAGEIFS(Table1[Duration],Table1[Delivered],">="&B13,Table1[Delivered],"<"&B13+7),0)

Thanks
Rob H
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41742631
Now that is bizarre, I was going to comment on RGOnzo's formula being mathematically incorrect but it does actually give the same results as I get with the formula I posted.

Syntax of mine is  AVERAGE(Date 1 - Date 2) ie Average duration

whereas yours are looking at (Sum of Date Range1/Count of DateRange1)  - (Sum of Date Range2/Count of DateRange2);  (ie Average Date1 - Average Date2)

I was always of the opinion that Averages and Products had to be done at the source level to ensure that the placement of the operators ( * or /) were in the right place but it seems to not matter in this case.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41742675
Just looked at the file uploaded by xtermie and I see they have done the same as me, calculated duration and then done the average of that. Apologies to xtermie if it seemed I was copying. No points to me for that part, maybe just for the Table suggestion.

Slight difference with my suggestion is that I have used the IFERROR function.

The syntax for that being: IFERROR(Formula,False)

Whereas xtermie has used combination of IF and ISERROR

The syntax for that being IF(ISERROR(Formula),False,Formula)
0
 
LVL 18

Expert Comment

by:xtermie
ID: 41742700
Hey Rob, no need to apologize, we are probably just thinking alike :)
Hopefully dabug80 will find our suggestions/solutions helpful and will resolve their issue.
0
 
LVL 1

Author Closing Comment

by:dabug80
ID: 41743476
This is an excellent solution. It's perfect as it doesn't use an additional helper column (which I can't add due to a CSV dump). Thanks for your great help.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question