Is there a way to get an average of selected cells (all the Children of a column) only if they are not blank? I am stuck and trying to automate the functions as much as possible. Thanks a ton in advance!
Hi Teresa,
Try something like this.
=AVG(COLLECT(CHILDREN(Column@row); CHILDREN(Column@row); NOT(ISBLANK(@cell))))
The same version but with the below changes for your and others convenience.
=AVG(COLLECT(CHILDREN(Column@row), CHILDREN(Column@row), NOT(ISBLANK(@cell))))
Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.
Did it work?
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
I am trying to pull over a date from one sheet, based on the lookup value and return a date from another smartsheet. I am getting unparseable at the moment from this formula. Also the column ([PIN]@row) reference it not lighting blue. =VLOOKUP([PIN]@row ],{FD Filers' list - PIN#ref},6,false) Any suggestions?
I want to take a smart sheet and have a reminder date column. East task that I have I want it to calculate off the duration to give me a reminder to do the task. Ex: Send pricing → Nov 1 of the year before the class Send brochure → 6 months before class date Check website for class → 90 days before class date Check in for…
Hoping that the community can explain this issue I cannot solve (nor can AI). Today we added a Y2026 Savings column to our sheet. Column formulas were all updated as well to look for values with "effective dates" in their corresponding years. Sheet Summary (for Dashboard) formulas broke returning "INVALID DATA TYPE". Tried…