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.
Best Answer
-
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))
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))
-
NateP ✭
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.
所以当前公式上面是伟大的,我只是需要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.
Help Article Resources
Categories
Check out theFormula Handbook template!
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/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":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109490,"type":"question","name":"HAS exact match within multiselect - Numbered Values","excerpt":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains))) formula, but having some false positives show up wherever a partial match is found. I later found some suggestions that (has) would be more…","snippet":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains)))…","categoryID":322,"dateInserted":"2023-08-25T19:26:32+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":154049,"lastUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":17,"score":null,"hot":3386003780,"url":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","format":"Rich","lastPost":{"discussionID":109490,"commentID":392694,"name":"Re: HAS exact match within multiselect - Numbered Values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392694#Comment_392694","dateInserted":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+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\/KJPRLKL2FW16\/capture-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:49:35+00:00","dateAnswered":"2023-08-25T23:58:23+00:00","acceptedAnswers":[{"commentID":392688,"body":"
Hi, <\/p>
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/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":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+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,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"