Broadcasting

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 fourth 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}

In Excel 2010 or later, certain functions are meant to be replaced with new functions to provide better results and names which (theoretically) make more sense. For example, the RANK function , which generates a rank for number against a range of numbers is listed as a compatibility function, and is replaced by the RANK.AVG and RANK.EQ functions.

Compatibility functions can still be used in later versions of Excel to ensure backward compatibility. However, Microsoft recommends that users deploy the newer functions (compatibility function replacements) when backward compatibility isn’t required.

Not all new functions with similar names indicate compatibility functions. For example, the NETWORKDAYS function , which calculates workdays between dates, is not listed as a compatibility function even though NETWORKDAYS.INTL is a newer function with the same purpose. The NETWORKDAYS.INTL simply provides more flexibility, allowing users to customize which days of the week are treated as weekends (i.e. non-work days).

Microsoft maintains a list functions with a category for compatibility functions .