@Pete Wright
Array formula have been around for longer than Excel but they are not really an End-User-Computing technique and their use has not been optimised in the same way that single-cell relative referencing has. Also, they are not discoverable in the sense that one might select the region for the results and commit the formula using
Ctrl+Shift+Enter, just to see what happens.
Reference material might include Chip Pearson's legacy
www.cpearson.com
and Mike Girvin
Over 3,300 Excel How To Videos from Beginner to Advanced, including downloadable Excel files to practice and pdf notes to summarize concepts - all free at Yo...
www.youtube.com
In the most recent versions of Office 365 everything has changed and now array formulas are the default (
no more CSE). A formula within a single cell can now return an array result which will display by '
spilling' across adjacent blank cells. Either way,
all calculation within Excel is based upon 2D arrays; a single cell simply gives rise to a very small array.
You are correct that multiplying a 2D range by a constant will multiply each value in the range by the constant (24 in this case) and return the result as an array. Similarly, the formula
= IF( hours > 40, hours - 40, 0 )
tests each element of 'hours' against 40 and then either subtracts 40 or returns 0 as the elements of the output array.
As for defined Names,
every name refers to a formula. A formula of the form '
=$A$5:$C$7' returns an absolute range reference but any valid formula may be given a Name and will, in general, return an array. Simple examples include named constants such as '
= 24' (the number of hours in a day) or '
={1,1}' (a row array comprising two 1s) and '
={1;1}' (a column array similarly comprising two 1s). When Excel encounters a Name within a calculation, it simply replaces it by the formula from its '
refers to' text and evaluates that.
The
MMULT function is an overly mathematical function that I have used to sum individual rows of a 2D array. I have an add-in that allows me to use
= SUMROWS( Overtime ) / 24
but, in general, Excel does not support operations involving 2D arrays at all well because it is assumed the user will want to manually select rows one by one for summation,
MMULT performs much the same calculation as multiple
SUMPRODUCTs but with the first array transposed and it gives an array output.
I hope this helps. It is the way I have worked for years but, on this forum, it probably causes more confusion than enlightenment overall.