Exceljet

Quick, clean, and to the point

Broadcasting

Excel formula broadcasting example

Broadcasting is an array calculation behavior in Excel formulas. It refers to a step during formula calculation where a smaller array is "filled" with duplicate values to create an array with the same dimensions as a larger array in the formula. In essence, Excel resizes arrays to be the same size.

In the example show, cells F5:F7 contain this multi-cell array formula:

=B5:B7*D5

The first range results in an array with 3 items. The second range results in an array with only 1 item. Broadcasting occurs when the second array is resized to contain 3 items, {2;2;2}, to match the dimensions of the first array {2;4;6}:

=B5:B7*D5 // starting formula
={2;4;6}*2 // initial evaluation
={2;4;6}*{2;2;2} // broadcasting
={4;8;12} // final result

Notice Excel simply repeats the value 2 during the operation. Broadcasting only occurs when the value to copy is unambiguous. When it's not clear what value to copy, Excel will use #N/A. For example, if we multiply {2;4;6;8}*{2;2}, the second array needs to be re-sized, but because it's not possible to know what values to use for third and forth elements, Excel fills with #N/A:

={2;4;6;8}*{2;2}
={2;4;6;8}*{2;2;#N/A;#N/A}
={4;8;#N/A;#N/A}

For more information on Excel's "array calculation behaviors", see this presentation by Joe McDaid on array formulas.