Apr 01 2020 04:33 AM
I've got a spread sheet where I track defective parts in one column and the corresponding costs associated with each instance in another column. Because these parts are inspected in small batches there are frequent duplicate values of multiple parts, and often there are ties, ie part xxx, yyy and zzz may each show up 3 different times on this list. I want a formula to identify the part with the highest frequency, and when there are ties I want to break that tie by highest total cost for each part. Right now I'm using INDEX, MODE.MULT and MATCH to identfy all duplicates and the total cost of each duplicate part, but considering this is a rolling 7 day record I really just want the most costly part to help quickly identify what job processes need to be looked at for issues. Is there a single formula I can use to do this?
Apr 01 2020 05:24 AM
You don't mention the MAX function as one you've tried to incorporate. From what you've described, not seeing your actual sheet, it's something I'd suggest....
Apr 01 2020 06:37 AM
Apr 01 2020 06:51 AM - edited Apr 01 2020 06:52 AM
I'm hoping to find a single (complex) formula that might do this, if that's possible that is.
Single complex formulas are possible, of course. They're also nightmares. Most texts on Excel warn against them unless they're unavoidable. They're awfully fun to write, cause for celebration when they first work.
But then three months later when you need to, uh, fix something again, they're oh-so-hard to deconstruct and remember what (this (nested function (did inside of (yet another nested conditional, (etc., (etc., ))))))
Far preferable (IMHO) to write formulas in adjacent columns (helper columns) that finally deliver the desired result, each of which can be comprehended at a glance.
Apr 01 2020 07:06 AM