I need help writing a formula.

Elayne Smith · 06/11/23

Logic

if cell b >0, then calculate the Sum Cell b-cell a

Carson Penticuff · 06/12/23

Yes I do get a circular reference. What I want to do if possible is if the month = $0 leave it as $0. but if the month is greater than $0 then take that amount and subtract it from the previous months amount to get the difference.<\/p>","bodyRaw":"[{\"insert\":\"Yes I do get a circular reference. What I want to do if possible is if the month = $0 leave it as $0. but if the month is greater than $0 then take that amount and subtract it from the previous months amount to get the difference.\\n\\n\"}]","format":"rich","dateInserted":"2023-06-12T14:15:49+00:00","insertUser":{"userID":162272,"name":"Elayne Smith","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Elayne%20Smith","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!z_neMx7Hvj4!mVDfnuqtMWQ!RH9Ly5uQ-po","dateLastActive":"2023-06-13T12:50:51+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380093#Comment_380093","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/380093#Comment_380093

You would need to add another column to be able to make that calculation.

ker9 · 06/11/23

Hi@Elayne Smith

Please try this:

=IF([ColA]@row > 0, [ColB]@row - [ColA]@row, 0)

If ColA is <=0 then it will return 0

(change "ColA" and "ColB" to your actual column names)

Hope this helps!

Elayne Smith · 06/12/23

It gave me back #Circular Reference

this is the formula I made

=IF([email protected]> 0,[email protected]-[email protected], 0)

It did not change if I put the row name in brackets [ ]

Carson Penticuff · 06/12/23

Are you putting the formula into the May or April row?

Elayne Smith · 06/12/23

into the May row

Ray Lindstrom · 06/12/23

@Elayne Smith,

Which column are you pasting this formula into? Sounds like you're executing this formula in cell b if you're getting a circular reference. This formula won't work in cell/column b because it's the cell being evaluated and written to.

Elayne Smith · 06/12/23

Yes I do get a circular reference. What I want to do if possible is if the month = $0 leave it as $0. but if the month is greater than $0 then take that amount and subtract it from the previous months amount to get the difference.

Carson Penticuff · 06/12/23

Yes I do get a circular reference. What I want to do if possible is if the month = $0 leave it as $0. but if the month is greater than $0 then take that amount and subtract it from the previous months amount to get the difference.<\/p>","bodyRaw":"[{\"insert\":\"Yes I do get a circular reference. What I want to do if possible is if the month = $0 leave it as $0. but if the month is greater than $0 then take that amount and subtract it from the previous months amount to get the difference.\\n\\n\"}]","format":"rich","dateInserted":"2023-06-12T14:15:49+00:00","insertUser":{"userID":162272,"name":"Elayne Smith","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Elayne%20Smith","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!z_neMx7Hvj4!mVDfnuqtMWQ!RH9Ly5uQ-po","dateLastActive":"2023-06-13T12:50:51+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380093#Comment_380093","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/380093#Comment_380093

You would need to add another column to be able to make that calculation.

Elayne Smith · 06/12/23

Ok, thank you!

Ray Lindstrom · 06/12/23

I believe once you reference that other column from the May (cell b) column, your circular reference error will return.

I need help writing a formula.

Best Answer

Answers

Help Article Resources

Categories