How do I fill a row based on the number of filled columns in the row above?

Question

Here's my current spreadsheet:

Rows 1 and 3 are the input, and I want to fill row 4 automatically based on how many columns there are in row 3. Example end result:

The "discount factor" is just (1 + (DiscountRate/100)) ^ n, where n = 1 underneath the first cash flow, n = 2 underneath the second cash flow, etc. I want the "discount factor" row to be automatically filled based on how many cash flows there are. So if I add another column of data to row 3, row 4 should automatically populate its column as well:

Which spreadsheet formulas can I use to automatically fill row 4 based on the number of filled columns in row 3?

Answer 1

If you have Excel O365, you can use this formula:

B4: =(1+(Discount_rate/100))^SEQUENCE(,COUNT($3:$3))

Because of the dynamic array feature, it will automatically spill right as far as there are entries in row 3.

This assumes that the only entries in Row 3 are a label in A3, and cash flows in the subsequent columns.