Using a cell instead of a range for IF statement?

Jaime Holder ✭✭

10/20/23 inFormulas and Functions

Hi all - looking to average a range based on whether or not a neighboring cell has a value of greater than zero - all I get are errors.

any help is appreciated, TIA

0 ·

Best Answer

JamesB ✭✭✭✭✭

10/20/23 Answer ✓

@Jaime Holder

Try this:

=IF([# of SO's]@row > 0, AVG([%age]$1:[%age]8), " ")

0 · Share on Facebook Share on Twitter

Answers

JamesB ✭✭✭✭✭

10/20/23 Answer ✓

@Jaime Holder

Try this:

=IF([# of SO's]@row > 0, AVG([%age]$1:[%age]8), " ")

0 · Share on Facebook Share on Twitter
Jaime Holder ✭✭

10/20/23

That solved it, thank you!

0 · Share on Facebook Share on Twitter
JamesB ✭✭✭✭✭

10/20/23

Your Welcome

0 · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Hi @Camila Parker<\/a> <\/p>

See if this works for you:<\/p>

=AVG(COLLECT([Act. Hours]:[Act. Hours], Status:Status, =\"Complete\", [Deck Intensity]:[Deck Intensity], =[Deck Intensity]@row))<\/p>

Hope this helps!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":112023,"type":"question","name":"How do I add days to an existing date?","excerpt":"I have read through this community about the same question, however, the solutions haven't seemed to work for me. I'm working on a Debt Snowball sheet to calculate and estimate when my bills will be paid off. I can use EITHER the Months or Days column from the DATE column. For example - need the formula to add 8 months (or…","snippet":"I have read through this community about the same question, however, the solutions haven't seemed to work for me. I'm working on a Debt Snowball sheet to calculate and estimate…","categoryID":322,"dateInserted":"2023-10-23T16:13:53+00:00","dateUpdated":null,"dateLastComment":"2023-10-23T21:27:09+00:00","insertUserID":168908,"insertUser":{"userID":168908,"name":"Jenna P","title":"Manager, Design","url":"https:\/\/community.smartsheet.com\/profile\/Jenna%20P","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3z9NP0Qgg_o!mf1anoANKcQ!qInc4lLGO-V","dateLastActive":"2023-10-23T21:21:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":168908,"lastUser":{"userID":168908,"name":"Jenna P","title":"Manager, Design","url":"https:\/\/community.smartsheet.com\/profile\/Jenna%20P","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3z9NP0Qgg_o!mf1anoANKcQ!qInc4lLGO-V","dateLastActive":"2023-10-23T21:21:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":18,"score":null,"hot":3396175262,"url":"https:\/\/community.smartsheet.com\/discussion\/112023\/how-do-i-add-days-to-an-existing-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112023\/how-do-i-add-days-to-an-existing-date","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":112023,"commentID":401250,"name":"Re: How do I add days to an existing date?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401250#Comment_401250","dateInserted":"2023-10-23T21:27:09+00:00","insertUserID":168908,"insertUser":{"userID":168908,"name":"Jenna P","title":"Manager, Design","url":"https:\/\/community.smartsheet.com\/profile\/Jenna%20P","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3z9NP0Qgg_o!mf1anoANKcQ!qInc4lLGO-V","dateLastActive":"2023-10-23T21:21:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/VA3KH8XYWXBA\/snowball-png.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/VA3KH8XYWXBA\/snowball-png.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/VA3KH8XYWXBA\/snowball-png.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/VA3KH8XYWXBA\/snowball-png.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/VA3KH8XYWXBA\/snowball-png.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/VA3KH8XYWXBA\/snowball-png.png"},"alt":"Snowball.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-23T23:08:08+00:00","dateAnswered":"2023-10-23T16:22:41+00:00","acceptedAnswers":[{"commentID":401195,"body":"

Hi @Jenna P<\/a>,<\/p>

If your DATE and Paid off by (est.) columns are both set to date then in the latter put the following formula:<\/p>

=Date@row + [Days rounded up]@row <\/p>

You will need to copy\/paste the date into the other rows for this to work, but you can then convert it to a column formula.<\/p>

Alternatively:<\/p>

=Date$1 + [Days rounded up]@row<\/p>

(change the 1 for the row number of your date). Here you can't convert to a column formula, but you can copy\/paste or drag it down.<\/p>

Pick whichever option you prefer! 🙂<\/span><\/p>

Hi @Paul Newcome<\/a> - yes, please see below. Anything else I might be doing wrong?<\/p>

Column which captures the date when any of the rows are put in any of the three statuses above -<\/p>

\n \n $\"image.png\"$ <\/img><\/a>\n <\/div>\n<\/div>\n

<\/p>

Column where I have daily dates to map the data for the time series line chart - <\/p>

\n \n $\"image.png\"$ <\/img><\/a>\n <\/div>\n<\/div>\n

<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">