This course teaches how to install and configure Windows Server 2012 R2. It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).
Excel: Average date difference of a range
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,
pls try
ee-average-datesV1.xlsx
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),"-")
ee-average-datesV1.xlsx
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?
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?
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);"-")
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
ee-average-dates_wexample.xlsx
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:$D
You could adjust the duration to only include working days if so required.
Assuming Duration in column D, formula in B14 and then copied across:
=IFERROR(AVERAGEIFS($D3:$D
You could adjust the duration to only include working days if so required.
To overcome the expanding range, convert the list to a table; then the above formula will become:
=IFERROR(AVERAGEIFS(Table1
Thanks
Rob H
=IFERROR(AVERAGEIFS(Table1
Thanks
Rob H
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.
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.
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,
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,
Premium Content
You need an Expert Office subscription to comment.Start Free Trial