Formula (array?) to compute weighted average balance
 

I have data in 2 columns 1) Date and 2) the Interest Rate for the period
ending on the Date. The dates are irregular and cover many years. I need to
study multiple data sets and there are over 1500 rows in each set.

Given a 3rd column of dates (e.g., 1/1, 2/1, 3/1, etc.), I was hoping to
calculate the weighted average balance for each monthly period ending 1/1/,
2/1, 3/1, etc.

Can I do this with a single array formula?

Only way I have been able to do this is with about 5 columns and SUMIF's
which I think are slowing my computational speeds.

(I've fooled around with PivotTables, but I can't them to work reliably, I
think because I need to update with real time data frequently).

I've never posted before, but given what I've read, I'm optimistic!

Thanks in advance.

Formula (array?) to compute weighted average balance
 

Hi,

Why don't you show us a sample of the data (all three columns) and maybe
what the answer you want for the same it.

Cheers,
Shane Devenshire

"BeenHappier" wrote:

I have data in 2 columns 1) Date and 2) the Interest Rate for the period
ending on the Date. The dates are irregular and cover many years. I need to
study multiple data sets and there are over 1500 rows in each set.

Given a 3rd column of dates (e.g., 1/1, 2/1, 3/1, etc.), I was hoping to
calculate the weighted average balance for each monthly period ending 1/1/,
2/1, 3/1, etc.

Can I do this with a single array formula?

Only way I have been able to do this is with about 5 columns and SUMIF's
which I think are slowing my computational speeds.

(I've fooled around with PivotTables, but I can't them to work reliably, I
think because I need to update with real time data frequently).

I've never posted before, but given what I've read, I'm optimistic!

Thanks in advance.