Cross reference sheet average number of days between two columns/dates while excluding blank values

walcala

Hello,

So to be able to do this so far I have added a helper column to my main smartsheet. This column I called "Days Between" just counts the amount of days between column Referral Faxed Date and Eval Date.

Helper column code:

=([EVAL DATE]@row  - [REFERRAL FAXED DATE]@row )

This seems to be working fine but we don't always have the data for Eval Date yet and those rows are coming up as blank as a result. So when I tried making a metrics sheet and cross referencing… I wasn't able to get this formula to work (note, I selected the Days Between column twice as my first and second sources on the metrics sheet):

=AVERAGEIF({Days Between}, <>"", {Days Between})

Does someone have a better solution ? I'm a noob at formulas and self taught so I'm just trying my best. 😅

Also ~ Is there a way for the master's helper column to be a formula column only so when individuals add new data from their dynamic views — the formula automatically applies?

Thanks x9000

-Wendy

SueinSpain

when you write a formula you can right click in the field and make it a column formula so it is applied to every row e.g

then Convert to Column Formula (bottom option)

and you can see the function and details in column

With regards to the blank and not returning data, I would suggest using IFERROR so that you can set a default. For example I look up a price from a product table based on the product code. If the product code cannot find a match I want a default price of £0.00 rather than and INVALID returned. So My formula reads:

=IFERROR(INDEX(COLLECT({Product Code - Details Buying Price 1}, {Product Code - Details Product Code Helper}, [Product Code Helper]@row ), 1), 0)

I built my INDEX(COLLECT statement first so I knew it worked and then added the IFERROR around it to cater for not getting and information returned.

Hope this helps