Need Help With A Formula

I cannot figure out what I need to do to get around this circular reference. I'm needing New Total to either subtract or add based on which column has an amount as you can see in the formula. I need that as a Column Formula in New Total so it does it every time. I understand why it's happening, just not sure if there is a way around it with a change to my formula :

=[New Total]@row - [9mm Duty Ammo Received]@row + [9mm Practice Ammo Received]@row + [Initial Count]@row - [9mm Duty Ammo Returned]@row - [9mm Practice Ammo Returned]@row

That is the formula where the circular reference is.

Any ideas??? Thank you! Sean

Find more posts tagged with

Accepted answers

All comments

Ape_Man819

A few possible solutions:

1. Use a Helper Column for Running Total

If you're trying to maintain a running total (like inventory), you need to reference the previous row's total, not the current one.

For example:

=IF(ROW() = 2, [Initial Count]@row + [9mm Practice Ammo Received]@row - [9mm Duty Ammo Received]@row - [9mm Duty Ammo Returned]@row - [9mm Practice Ammo Returned]@row,
[New Total]@row - 1 + [9mm Practice Ammo Received]@row - [9mm Duty Ammo Received]@row - [9mm Duty Ammo Returned]@row - [9mm Practice Ammo Returned]@row)

This assumes:

Row 2 is your starting row.
You calculate the first total from Initial Count.
Each subsequent row builds on the previous row’s [New Total].

2. Manual Input for Initial Total

If you can’t reference previous rows, you might need to:

Manually input the starting total.
Use a formula that does not reference [New Total]@row.

Example:

=[Initial Count]@row + [9mm Practice Ammo Received]@row - [9mm Duty Ammo Received]@row - [9mm Duty Ammo Returned]@row - [9mm Practice Ammo Returned]@row

This works if each row is independent and not part of a running total.

Hope this helps.

sean.hagan1088

@Ape_Man819 this looks like exactly what I'm needing! Thank you for getting back to me ! I need a running total like your first example. I plugged in the formula you have and I'm getting a syntax error somewhere. Is this =IF(ROW() referencing my Row Id column? Or would that just be like the standard @row ? Also would I need both formulas from lines 1 and 2 to replace my current formula in the New Total column?

Ape_Man819

A few possible solutions:

1. Use a Helper Column for Running Total

If you're trying to maintain a running total (like inventory), you need to reference the previous row's total, not the current one.

For example, in Smartsheet or Excel (if supported):

This assumes:

Row 2 is your starting row.
You calculate the first total from Initial Count.
Each subsequent row builds on the previous row’s [New Total].

2. Manual Input for Initial Total

If you can’t reference previous rows, you might need to:

Manually input the starting total.
Use a formula that does not reference [New Total]@row.

Example:

This works if each row is independent and not part of a running total.

Ape_Man819

1 goes into the original row, then 2 goes into the total row.
As for =IF(ROW() referencing my Row Id column? Or would that just be like the standard @row ? I would try the @Row, that is how I ran this, but I also think it could be determined by your sheet. Try the @Row first and change if needed.

sean.hagan1088

I definitely think what you're saying is right, I just can't get it to work. I took out all my formulas and did calculations by hand. I need it to look like this:

But I need the New Total column to update the running total each time and then when the new row is added use that running total in the new calculation. The problem is I get the CircularReference error when adding that initial starting total of 10,000 in this example, because there is nothing before row 001. Trying to avoid manually entry, if possible of New Total each time.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.