Average the number of days

Dave Godfrey
Dave Godfrey ✭✭✭✭
edited 12/09/19 in Smartsheet Basics

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?

· Share on FacebookShare on Twitter

Comments

  • steven.reed14881
    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

    · Share on FacebookShare on Twitter
  • Dave Godfrey
    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

    Dates.JPG

    · Share on FacebookShare on Twitter
  • steven.reed14881
    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

     

    · Share on FacebookShare on Twitter
  • J. Craig Williams
    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

    · Share on FacebookShare on Twitter
  • Paul Newcome
    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.

    Untitled.png

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter

Categories

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video