On Jan 11, 3:10 am, Lori wrote:
Maybe this in C4, and copy down to other rows...

Well, only if I have less than $9E+99 <g. Seriously....

=ROUNDUP(SUMPRODUCT(--(MATCH(9E+99,C$1:C3)
<=ROW(C$1:C3)),A$2:A4-A$1:A3,B$1:Â*B3)*D4/365,-2)

Thanks. That does seem to work. But I do not understand it would. Can you
(or someone) explain the theory of operation?

I only need an explanation of the MATCH() usage. I understand that if
MATCH() finds row x, then --(x<=row(C$1:C3)) expands into an array of false
(0) for rows below x and true (1) for rows at and above x; and that array of
0s and 1s effectively cancels out uninteresting products of
(A$2:A4-A$1:A3)*(B$1:B3).

But according to the help page, in that form, MATCH() will search from C$1
to the row above (C3, in this case) for the __largest__ number less than or
equal to 9E+99. In this form, MATCH() expects the lookup-array to be in
ascending order.

That is true for the column C in my example; but that is only by
coincidence. Moreover, I prefer to search column D, where the figures
clearly are not in ascending order. Finally, I am not interested in the
largest number, but in the nearest number above.

That said, I am surprised to find that using a lookup-array of random
percentages (with a random number of interstitial blanks, at least 1), the
MATCH() usage as you wrote it (substituting column D for column C) does work
exactly as I need it. That is, MATCH() always returns the nearest number
above, not the largest number in the array.

Why is that? Is it only an accident of implementation?

(I would expect that MATCH() uses a binary search algorithm, which normally
starts with the middle element. But unpredictable things happen in a binary
search if the lookup-array is not sorted. Nonetheless, I would be surprised
that it always determines that the last non-zero element of the array meets
the criteria.)

Thanks for any insight.

That said, I do like your structure of the SUMPRODUCT(), performing the
look-up only once. So if MATCH() does not really work in the final analysis,
I believe I am looking for a look-up function that simply searches backwards
sequentially -- or some other clever usage that has that effect.

Thanks again.