- Sumproduct() I’ve been doing it worng!!
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.
Wonderful! I even used it to do Not as well as Or.Posted 8 months agoStonerMember
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 RHPosted 8 months ago
Clever but not exactly readable.
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.Posted 8 months agoscaredypantsSubscriber
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.Posted 8 months ago
You must be logged in to reply to this topic.