Hello,
I've been trying to calculate average difference between two date columns without using any helper columns. Example workbook added.
The whole row should be ignored if column B or C contains a blank cell
Column A: Item name
Column B: Date when the item was opened
Column C: Date when the item was closed
As far as i have achieved is this :although it returns DIV/0 error.
{=AVERAGE(IF(ISNUMBER(B1:B100)*ISNUMBER(C1:C100),C1:C100-B1:B100))}
Thank you for any help and tips.
Last edited by baxius; 10-18-2021 at 04:48 AM.
To start with your dates are text! if they were date (global replace of . with / in columns B and C and the formula works. If this is not possible a formula can be written but it is more complicated
All your isnumber formulas are false (as they are text) hence the average is of no values and so divides by nothing (the error)
Last edited by davsth; 10-18-2021 at 05:19 AM.
Thank you for your quick insight. These "Dates" are actually returned from an array with VBA. I'll have to look into their formatting settings then. Thank you again and +rep. Solved for now.
Last edited by baxius; 10-18-2021 at 05:23 AM.
=AVERAGE(IFERROR(DATE(LEFT(C1:C13,4),MID(C1:C13,6,2),RIGHT(C1:C13,2))-DATE(LEFT(B1:B13,4),MID(B1:B13,6,2),RIGHT(B1:B13,2)),"")) as they stand, but far more painful
Yeah, not really the most optimal option. I'll try to figure out the formatting first. Cheers for your struggle.
Please try
=AVERAGE(IFERROR(SUBSTITUTE(C1:C13,".","-")-SUBSTITUTE(B1:B13,".","-"),""))
Ctrl+Shift+Enter
Wow, i actually didn't thought of that Bo_Ry. Thank you. Works like a charm.
Try this array formula
Formula:
=AVERAGE(IF((B1:B13<>"")*(C1:C13<>""),(SUBSTITUTE(C1:C13,".","/")-SUBSTITUTE(B1:B13,".","/"))))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks