SOLVED

Avergae of Date range

%3CLINGO-SUB%20id%3D%22lingo-sub-1571018%22%20slang%3D%22en-US%22%3EAvergae%20of%20Date%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1571018%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20two%20columns%20of%20dates%20and%20I%20need%20to%20be%20able%20to%20work%20out%20the%20total%20average%20without%20adding%20an%20additional%20helper%20column.%20Also%2C%20I%20need%20the%20formula%20to%20not%20count%20blank%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EE.g%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E%3CSTRONG%3EDate%20Received%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E%3CSTRONG%3EDate%20Sent%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E26%2F02%2F2020%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E05%2F03%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E28%2F02%2F2020%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E03%2F03%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E03%2F03%2F2020%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E11%2F03%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E04%2F03%2F2020%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E07%2F03%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E06%2F03%2F2020%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EAverage%20No.%20of%20days%20to%20send%3A%3C%2FSTRONG%3E%20Now%2C%20I%20know%20one%20formula%20is%20to%20do%3CBR%20%2F%3E%3CBR%20%2F%3E%3D%20AVERAGE(Date%20Sent%20-%20Date%20Received)%3CBR%20%2F%3E%3CBR%20%2F%3EHowever%2C%20this%20even%20takes%20a%20blank%20cell%20in%20to%20count.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20do%20this%20without%20including%20rows%20which%20do%20not%20have%20a%20date%20entered%20in%26nbsp%3B%3CSTRONG%3EDate%20Sent%3C%2FSTRONG%3E%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1571018%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1571064%22%20slang%3D%22en-US%22%3ERe%3A%20Avergae%20of%20Date%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1571064%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F703294%22%20target%3D%22_blank%22%3E%40Mo_Islam00%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20379px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F210660iB030C8DE419362AB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ethat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DAVERAGE(B2%3AINDEX(B2%3AB100%2CMIN(COUNTA(A%3AA)%2CCOUNTA(B%3AB))-1)-A2%3AINDEX(A2%3AA100%2CMIN(COUNTA(A%3AA)%2CCOUNTA(B%3AB))-1))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1571084%22%20slang%3D%22de-DE%22%3ESubject%3A%20Avergae%20of%20Date%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1571084%22%20slang%3D%22de-DE%22%3E%3DAVERAGE(C1%3AC6)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1571106%22%20slang%3D%22de-DE%22%3ESubject%3A%20Avergae%20of%20Date%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1571106%22%20slang%3D%22de-DE%22%3Eor%20a%20Matrix%20formula%20%3CBR%20%2F%3E%20s%3DAVERAGE(B1%3AB6-A1%3AA6)%20%3CBR%20%2F%3E%20Leave%20the%20cell%20editor%20with%20Ctrl%20%2B%20Shift%20%2B%20Enter%20instead%20of%20Enter%20alone.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1571135%22%20slang%3D%22en-US%22%3EBetreff%3A%20Avergae%20of%20Date%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1571135%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20doesn't%20work%20if%20some%20dates%20in%20column%20B%20are%20empty%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1571160%22%20slang%3D%22en-US%22%3ERe%3A%20Avergae%20of%20Date%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1571160%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F703294%22%20target%3D%22_blank%22%3E%40Mo_Islam00%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPrevious%20variant%20works%20if%20only%20blanks%20are%20at%20the%20end%20of%20second%20column.%20More%20simple%20and%20more%20reliable%20variant%20shall%20work%20if%20in%20the%20middle%20as%20well%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20509px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F210671i2BF0DEBCA12FF171%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewith%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DAVERAGE(IF(B2%3AB100%3D0%2C%22%22%2CB2%3AB100-A2%3AA100))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EYou%20may%20expand%20by%20dynamic%20ranges%20based%20on%20column%20A%20as%20in%20previous%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1571264%22%20slang%3D%22en-US%22%3ERe%3A%20Avergae%20of%20Date%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1571264%22%20slang%3D%22en-US%22%3EAah%2C%20great.%20thank%20you%20for%20double%20checking.%20You%20are%20right%2C%20previous%20only%20works%20with%20the%20last%20being%20empty%2C%20however%20this%20formula%20works%20perfectly.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1571423%22%20slang%3D%22en-US%22%3ERe%3A%20Avergae%20of%20Date%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1571423%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F703294%22%20target%3D%22_blank%22%3E%40Mo_Islam00%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1571637%22%20slang%3D%22en-US%22%3ERe%3A%20Avergae%20of%20Date%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1571637%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F703294%22%20target%3D%22_blank%22%3E%40Mo_Islam00%3C%2FA%3E%26nbsp%3BHow%20can%20the%20date%20sent%20be%20later%20than%20the%20date%20received%3F%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

 

I have two columns of dates and I need to be able to work out the total average without adding an additional helper column. Also, I need the formula to not count blank cells.

 

E.g

 

Date ReceivedDate Sent
26/02/202005/03/2020
28/02/202003/03/2020
03/03/202011/03/2020
04/03/202007/03/2020
06/03/2020 
  
  

 

Average No. of days to send: Now, I know one formula is to do

= AVERAGE(Date Sent - Date Received)

However, this even takes a blank cell in to count.

 

How can I do this without including rows which do not have a date entered in Date Sent?

 

Thank you.

8 Replies

@Mo_Islam00 

For such sample

image.png

that could be

=AVERAGE(B2:INDEX(B2:B100,MIN(COUNTA(A:A),COUNTA(B:B))-1)-A2:INDEX(A2:A100,MIN(COUNTA(A:A),COUNTA(B:B))-1))
=AVERAGE(C1:C6)
or a Matrix formula
{=AVERAGE(B1:B6-A1:A6)
Leave the cell editor with Ctrl + Shift + Enter instead of Enter alone.

@Nikolino 

That doesn't work if some dates in column B are empty

Best Response confirmed by Mo_Islam00 (New Contributor)
Solution

@Mo_Islam00 

Previous variant works if only blanks are at the end of second column. More simple and more reliable variant shall work if in the middle as well:

image.png

with

=AVERAGE(IF(B2:B100=0,"",B2:B100-A2:A100))

You may expand by dynamic ranges based on column A as in previous formula.

Aah, great. thank you for double checking. You are right, previous only works with the last being empty, however this formula works perfectly.

@Mo_Islam00 , you are welcome

 

@Mo_Islam00 How can the date sent be later than the date received??