A certain type of spreadsheet bliss attaches to relative ignorance. Know just one formulaic way around a task, and your decision rule requires no deciding: write the formula.
But know at least a couple of ways to get where you want to go, and you’ll need to break out the map and plot your best-course scenario, or at least try to. Last week’s post described one means, driven in part by a teaming of the OFFSET and CELL functions, for totalling the number of patients having to wait up to a specified number of weeks for treatment in an identified medical speciality, those data emanating from the National Health Service spreadsheet upon which we drew in the post. But alternative means toward that end are available; and why you’d make use of this as opposed to that one stands as a good question, the answer to which has a lot to do with the confidence you can marshal toward the approach. Find one formula among the options the easiest to write and you’ll likely be magnetized in that direction – even if in some absolute, textbook-ish sense, some other formula comes best in show for elegance.
In any case we can, in the interests of informed choice-making, review two other formula possibilities, braced by the corollary concession that still others may be camouflaged in the brush. Remember we’re interested in learning the number of patients in a medical speciality who waited a maximum, stated number of weeks for treatment (we’re continuing to address the workbook introduced last week). We can begin to put our second option into play by retaining the dropdown menu of medical specialities in E1 (founded on cells F4:F22) we forged last week (look here for a precis of dropdown menu construction, if you’re new to the idea. You don’t really need to name the range, though, in spite of the linked discussion’s advisory – at least not in our case, as its contents won’t be augmented). Then enter a week-waited number in D2, say 12, and for illustration’s sake select ENT from the speciality dropdown in E1:
Thus we’ve declared in interest in discovering the number of individuals who had to wait up to 12 before receiving ENT treatment. Then, enter this expression, say in H1:
=SUMPRODUCT((F4:F22=E1)*(COLUMN(G3:BG3)<=D2+6),G4:BG22)
This formula likewise calls for an exposition, needless to say. SUMPRODUCT, Excel’s quintessential off-the-shelf array formula, is perhaps the deepest of the application’s functions, its diffident, user-familiar tip (i.e., it multiplies pairs of values and proceeds to add them all) transmitting the weakest of signals about the iceberg immured beneath.
Here, SUMPRODUCT combs F4:F22 for the medical speciality – ENT – we ticked in E1. And you’ll observe that the search and find for ENT is pressed without any syntactical resort to the standard, conditional IF; that word is nowhere to be found in the formula. When it finds ENT – in F7 – the formula moves to examine row 7 for the values running across its contiguous, number-bearing cells, in columns G through BG (note too that the F4:F22=E1 phrase is couched in parentheses).
And that sweep through the columns takes us to the *(COLUMN(G3:B3)<=D2+6) piece of the formula. The star/asterisk reminds us that SUMPRODUCT continues to do what it’s been programmed to do – assign a value of 1 to its successful sighting of the requested medical speciality ENT (a sighting that first imputes the name TRUE to the finding in F7 – and in the Boolean language of array formulas, TRUE is next quantified into a 1). In turn, the other non-complying entries in F4:F22 are deemed FALSE, and incur a 0 as a result. COLUMN identifies the absolute column number of any cell reference; thus =COLUMN(X34) returns 24, for instance.
Befitting its multi-calculation, array formula character, COLUMN(G3:BG3) flags the column number of each of the G3:BG3 entries, asking if any of these equal or fall beneath the value 18, that number a resultant of the 12 we entered in D2 – plus 6, a necessary additive that squares our formula with the fact that the first column we’re inspecting – G – has a column value of 7. Adding the 6, then, to the 12 in D2 – the week wait figure – transports us to the 18th column of the worksheet, R. And R contains the Gt 11 to 12 Weeks data that marks the outer bound of our search. And any column that satisfies our criterion – any week equal to or less than 12 – likewise receives a TRUE/1 evaluation.
Remember again that, convolutions notwithstanding, SUMPRODUCT is about multiplication – and here the 1 assigned F7 is multiplied (remember the asterisk) by all columns in receipt of a 1. All other cells – that is, all the other medical specialties in the F column, and all the weeks in excess of 12 – receive a zero, and their multiplications yield zero and drop out of the formula. The remaining 1’s, so to speak, are multiplied by the ENT values for week 12 and before in cells G4:BG22, and ultimately added – because that’s what SUMPRODUCT does.
Again, we’re counting the number of ENT patients needing to wait up to 12 weeks for treatment, and in this case I get 190,480. Type a different week number in D2 and/or select a different speciality from the dropdown menu in E1, and the sum should respond accordingly. And because SUMPRODUCT is a homegrown Excel function, it stores itself into its cell via a simple strike of the Enter key – and not the storied Ctrl-Shift-Enter in which customized array formulas are obliged.
Hope that’s halfway clear – though halfway probably won’t help you write the formula when and if you need to write something like it some other time. This application of SUMPRODUCT is a good deal more thought-provocative that its simpler implementations, but if it makes you feel any better I had to think about it, too.
The point again is that SUMPRODUCT has delivered us down an alternate formulaic route to our answer, and whether it’s to be preferred to the OFFSET-mobilized variant we explored last post remains a good question.
But there are still other possibilities.
spreadsheetjournalism 
Leave a Reply