Getting MIN to work inside of an Array, or another solution

[DJMarky]

I have a file that has, in the third row, cashflows on which interest
is to be accrued (in this case, compounded at 10% per period). I am
trying to show the cumulative value of those cashflows plus accrued
interest, calculated in a single row. So, for example, in 7th period
the formula I came up with is:

=SUMPRODUCT(($B3:H3)*((1.1)^(COLUMN(H3)+1-COLUMN($B3:H3))))

Now, I need to introduce a dynamic limit by which interest is only
compounded for a certain period of time, denoted in cell A4. Trying to
limit the interest to three years, I tried:

=SUMPRODUCT(($B3:H3)*((1.1)^(MIN($A4,COLUMN(H3)+1-COLUMN($B3:H3)))))

However, the MIN function appears to be outside of the array as it
keeps returning "1." So, I think what is happening is that I'm
getting, where A4=3, Min(3,{7,6,5,4,3,2,1})=1 as opposed to the array
that I'm hoping for of
{min(7,3),min(6,3),min(5,3),min(4,3),min(3,3),min(2,3),min(1,3)} to
multiply by the array of cashflows.

I'm not tied in any way to my original formula and would also
appreciate wholesale new formulas that accomplish the same task as
well. The main point of the formula is to be able to multiple one
array of values by another array of time that puts a maximum on the
amount of time that has elapsed since the date of that cashflow. Just
to be clear, the result that I'm looking for would look something like
this:

Investment by Year: 1,0,1,0,0,0,1,... (it goes on for 50 or so
columns)
Ending Value by Year: 1.1, 1.21, 2.43, 2.54, 2.66,2.66,3.76,...
Where the calculation for the 7th data point should be 3.76 =
sumproduct{1*1.1^min(7,3), 0*1.1^min(6,3), 1*1.1^min(5,3),
0*1.1^min(4,3), 0*1.1^min(3,3), 0*1.1^min(2,3), 1*1.1^min(1,3)}

Thanks!

 

[CWatters]

I have a file that has, in the third row, cashflows on which interest
is to be accrued (in this case, compounded at 10% per period). I am
trying to show the cumulative value of those cashflows plus accrued
interest, calculated in a single row. So, for example, in 7th period
the formula I came up with is:

=SUMPRODUCT(($B3:H3)*((1.1)^(COLUMN(H3)+1-COLUMN($B3:H3))))

Now, I need to introduce a dynamic limit by which interest is only
compounded for a certain period of time, denoted in cell A4. Trying to
limit the interest to three years, I tried:

=SUMPRODUCT(($B3:H3)*((1.1)^(MIN($A4,COLUMN(H3)+1-COLUMN($B3:H3)))))

However, the MIN function appears to be outside of the array as it
keeps returning "1."

I tried putting =MIN($A4,COLUMN(H3)+1-COLUMN($B3:H3)) in cell A1 of a
blank sheet.

With $A4 = 1 I get A1 = 1. Similarly upto 7 then it remains at 7

eg $A4=8 gives A1=7

1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 7
9 7
10 7

 

[CWatters]

I tried putting =MIN($A4,COLUMN(H3)+1-COLUMN($B3:H3)) in cell A1 of a
blank sheet.

With $A4 = 1 I get A1 = 1. Similarly upto 7 then it remains at 7

eg $A4=8 gives A1=7

1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 7
9 7
10 7

However if I change it to

=1^(MIN($A4,COLUMN(H3)+1-COLUMN($B3:H3)))

it seems to return 1 regardless of the content of $A4.

 

[CWatters]

However if I change it to

=1^(MIN($A4,COLUMN(H3)+1-COLUMN($B3:H3)))

it seems to return 1 regardless of the content of $A4.

Duh of course it does. 1^anything allways = 1. My mistake.

2^(MIN($A4,COLUMN(H3)+1-COLUMN($B3:H3)))

works just fine.