Average days open

Luis Orosco

=AVERAGEIF([Closing Date]:[Closing Date], "", [Days ago]:[Days ago])

Im trying to average the amount of days a case is open by selecting the rows that have a blank closing day which means that they still open. but i keep getting #INVALID DATA TYPE

thank you in advance

Genevieve P.

Hi @Luis Orosco

I would suggest adding in a second criteria where a different column is not blank so that this isn't including the 10 blank rows at the bottom of the sheet.

Try something like this:

=AVG(COLLECT([Days ago]:[Days ago], [Closing Date]:[Closing Date], "", [Primary Column]:[Primary Column], <> ""))

Then, what column type are you putting the formula into? Make sure it's a Text/Number column.

Let me know if this works!

Cheers,

Genevieve

Genevieve P.

Hi @Luis Orosco

I would suggest adding in a second criteria where a different column is not blank so that this isn't including the 10 blank rows at the bottom of the sheet.

Try something like this:

=AVG(COLLECT([Days ago]:[Days ago], [Closing Date]:[Closing Date], "", [Primary Column]:[Primary Column], <> ""))

Then, what column type are you putting the formula into? Make sure it's a Text/Number column.

Let me know if this works!

Cheers,

Genevieve

Luis Orosco

Thank you!! that worked very well! I'm very new to smartsheet. I really love the support

Genevieve P.

No problem at all, I'm glad I could help! 🙂