Welcome!

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?

Find more posts tagged with

Comments

steven.reed14881

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

Dave Godfrey

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

steven.reed14881

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

J. Craig Williams

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

Paul Newcome

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Using single-sign-on with external partners
We want to implement single-sign-on at work and have received approval. However, our IT department wants all external partners to have/use Mayo credentials to log into Smartsheet. Not all of our external partners ever set foot on our campus and don't have our company credentials. I have a couple of questions. Has any other…
Cannot create new Chart from a report
I can create a Chart in Dashboard from sheet well but from Report is not working now. I just try with simple Report (just contain 2 column Primary and Number) but cannot make the chart. Is there any changes recently right, because I haven't worked with Smartsheet dashboard for a long time and I remembered that I could do…
Anyone have the link to register for the Core Product Exam without the Learning Pass.
Cant find this anywhere