Replacing a What-If Data Table with One Formula

August 26, 2022 - by Bill Jelen

This example uses Broadcasting to calculate a number of monthly car payments using the PMT function. The interest rate payment is a single value. The term argument is expecting a scalar, but the formula passes a 4x1 array. The present value is passing a scalar, but the formula passes a 1x5 array.

Through the magic of Broadcasting, Excel knows that is has to generate a 4 row x 5 column array and pass those 20 calculations to PMT. I wondered if the conditional formatting color scales would work on the formula results, and as you can see here, they do!

This formula replaces a 2-variable data table. Across the top, various car prices of $20K through $80K. Down the side, terms of 36, 48, 60, and 72. A single formula =PMT(5.25%/12,A4:A7,B3:F3) returns all of the loan payments into a four-row by five-column range. — Figure 638. A single Dynamic Array formula replaces a data table.

This article is an excerpt from Power Excel With MrExcel

Title photo by Andrew Neel on Unsplash