Sumproduct() I’ve been doing it worng!!

Home Forum Chat Forum Sumproduct() I’ve been doing it worng!!

Viewing 12 posts - 1 through 12 (of 12 total)
  • Sumproduct() I’ve been doing it worng!!
  • Rob Hilton
    Member

    Well, not *all* that worng, but some of my past work could have been more efficient.

    Mr Stoner might like this; a couple of you other geekazoids might appreciate it too.

    Or you might already be aware and I’m well behind the times…

    I’m surprised I’ve not come across it before, but happened to stumble on it today while looking for a way to fudge wildcards into Sumproduct/be able to avoid using a UDF to get around its inability to reference Indirect()/not have to use arrays of strings/use cell references and ting.

    https://www.sumproduct.com/thought/multiple-criteria-with-or

    Wonderful! I even used it to do Not as well as Or.

    Premier Icon thepurist
    Subscriber

    Clever but not exactly readable. I’d put it in the same category as complex array formulae – avoid unless absolutely necessary. Sometimes I’d prefer to use a hidden sheet/range to improve readability and simplify maintenance

    Premier Icon Stoner
    Subscriber

    this thread is relevant to my interests.

    XOR has passed me by as I primarily moved to google sheets in 2005, but it is a valid function in google sheets too.

    I havent had to do any bulk data handling for years, but its useful to park a new technique out the way, not that I can think of many opportunities to look for OR in multiple criteria just yet…

    cheers for the thread though RH

    Rob Hilton
    Member

    Clever but not exactly readable.

    FUNSPONGE!!!!111!!!

    Rob largely disagrees, as most of his work is way beyond the average user anyway and works nicely-nice. One of the points of this method is that it is infinitely more readable than nested Sumproduct()s.

    He/she/it does, however, agree that if the piece of work is to be passed on to someone/thing that wouldn’t be able to follow it, then a step-by-step process for calculations is preferable.

    Most of the people one comes across (NHS) are stuck in the realm of scrappy data & copy/pasting, so can’t follow a thing one does, anyhoo. One thinks one might as well use the tools available to myself.

    Rob Hilton
    Member

    FOL!

    *Ones

    What’s with all the <div>s all of a sudden.

    I’m gonna try editing that mess :/

    Oh, that’s more betterer 🙂

    doris5000
    Member

    well I just discovered =RAND().

    Stick that in your pipe and smoke it.

    Premier Icon tomhoward
    Subscriber

    And I thought all the IT nerds had buggered off since the update….

    Rob Hilton
    Member

    well I just discovered =RAND().

    You don’t get many of them to the pound

    doris5000
    Member

    well I just discovered =RAND().

    You don’t get many of them to the pound

    about 0.4859845313, I believe.

    No wait – 0.9873415546

    Premier Icon scaredypants
    Subscriber

    Most of the people one comes across (NHS) are stuck in the realm of scrappy data & copy/pasting, so can’t follow a thing one does, anyhoo. One thinks one might as well use the tools available to myself

    I’m an (un)enthusiastic amateur excelist working in the NHS trying to bugger up my own data in the absence of any meaningful support.  MrExcel is my real dad.

    hodgynd
    Member

    What a waste of 10 seconds ..

    There was so much more I could have done with that time …

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic.