![How do I fill a row based on the number of filled columns in the row above?](https://linux22.com/image/1631445/How%20do%20I%20fill%20a%20row%20based%20on%20the%20number%20of%20filled%20columns%20in%20the%20row%20above%3F.png)
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?
答案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.