Average the number of days
I am trying to work out the average number of days quotes are being taken to be submitted.
The columns i am working with are Enquiry Date and Date Quote Submitted.
I have done a Sum formula to count the number of days taken (=SUM[Date Quote Submitted]1 - Enquiry Date]1
but because not every enquiry has a date submitted these rows have #INVALID OPERATION so the +AVG for the column doesnt work.
Is there another way i could do this?
Comments
-
Dave,
I just mocked this up, and I am not sure why you are getting that error. It worked just fine for me. To simplify it a bit, I used the following formula to calculate the elapsed days (column titled 'Aging"): =[Date Quote Submitted]1 - [Enquiry Date]1. Then this is the formula I used to determine the average number of days at the bottom of the Aging column: =[Date Quote Submitted]1 - [Enquiry Date]1. Good luck with this.
Steve
-
Hi Steve,
I have just tried your example and i think my problem is that not every row has a quote submitted so the date box is blank.
I have attached a screen of the problem i have.
Thanks
Dave
-
I think I may have figured out your issue. Initially I thought it must be something with your formula, because the sheet I used to try your scenario on also included blanks. But then I tried something out. I changed one of the date columns to be a text/number type, and it changed my calculated fields to #INVALID OPERATION. Maybe that is the issue here?
Steve
-
Something very odd here.
If the problem were the column type, then all of the calculations should be returning #INVALID OPERATION.
If the second valid quote date (July 12th) were a Text/Number, then it should throw an error when trying to perform math on it (unless the other column is blank)
Your original posting showed different column names than the image shows.
What formula is currently in use?
Craig
-
I've tried it as well with no issue. Maybe send us a screenshot of the actual formula in the cell in the row that is giving you the error as below.
