Overide a formula with another

I have the following formula that works great because I can add estimated times in until real-time data is available that is referenced to another sheet. Except sometimes I have referenced data that is really off and I want to use the estimated time until I can research the issue or until new data is put in the other sheet. I would like to include a way to use the Override bend time check box to input the estimated bend part time back in as the value instead of the referenced values from my other sheet.

=IF(AVERAGEIF({Part # reference 1}, SKU@row, {Bending reference 1}) <> 0, AVERAGEIF({Part # reference 1}, SKU@row, {Bending reference 1}), [Estimated Bend Part time]@row)

I'm assuming I just need to put an IF somewhere but I'm really new to formulas so I just don't know where.

In the example below, all three SKUs should have similar values. JL6550 and JL6540 both had real-time data but JL6540 was way off.

Find more posts tagged with

Formulas

Accepted answers

NateP

I played around with it and got it to work. Just added in the italicized section in front of my original formula.

=IF([Override bend time]@row = 1, [Estimated Bend Part time]@row, IF(AVERAGEIF({Part # reference 1}, SKU@row, {Bending reference 1}) <> 0, AVERAGEIF({Part # reference 1}, SKU@row, {Bending reference 1}), [Estimated Bend Part time]@row))

All comments

Summer

Hi @NateP,

When is the "Bending Part Time (min) considered way off? Your example shows quite the discrepancy, but we need parameters if we're going to build it into the formula.

NateP

I played around with it and got it to work. Just added in the italicized section in front of my original formula.

NateP

https://community.smartsheet.com/discussion/comment/319489#Comment_319489

Summer, I want the Override bend time checkbox to be completely manual. I will check that box when I see something out of whack.

So here's how I'm using this information. I have a SKU that I'm collecting various manufacturing times in the process. Sometimes that data is not available yet....my workers have not worked on the product but I need to put something in so I can at least get some ideas. I put in my best educated guess (usually based off another similar product) in the Estimate Bend Part Time cell and my formula in the Bending Part Time (min) pulls that value. When the workers finally get to that product, the data they enter will override the estimated value. Sometimes, they enter information wrong, or they take much longer than they should. I can usually tell off the top of my head that these values are completely wrong so I want to be able to check the box and have it revert back to the Estimated value rather than the actual value from them. These cells are all part of much larger formulas so these big discrepancies can throw off my larger formulas a lot so it's basically a "hold until I can fix the issue" type box. This will help me to identify problems in the manufacturing process and address them.

So the current formula above is great, I just need to add in a clause that says, if the checkbox is checked, use the estimated value instead of the actual data, but in all other cases, use the actual data if it exists (ie not 0) , if not then use the estimated.

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.