We sometimes look for non-array (i.e. non-CSE) versions of constructions which would normally require array-entry. Our reasons for doing so may be varied:
1) We may feel that it improves spreadsheet performance (sometimes true, sometimes not)
2) We perhaps have a dislike for having to use the required keystroke combination necessary for committing array formulas
3) We may simply be interested from a theoretical point of view
Last week I set readers the challenge which can be found here.
This one was perhaps a little less complex than ones I’d set in previous weeks, though of course it would still, in my opinion, fall within the boundaries of what I would deem “advanced Excel”.
It also demonstrates some techniques which we can apply to solving problems involving non-contiguous ranges, and in particular tell us which functions may be applicable to such set-ups.
Two good solutions received from John Jairo V and cyrilbrd (and Bill‘s was practically there as well, but for a small amendment – and the fact that I didn’t structure the question in full to begin with – sorry!).