If statements, weighted averages, by dates, with cap = random concoction.

[Cole Parker]

Using a sample data set below:
I need to get a weighted average for adjusting rates in the future.
The rate will be tethered to Cell B2 which is NY Prime + the spread column, with a cap of max increase of 2% over their CURRENT rate, but i need a weighted average of the spread by the column E, Current Balance... i will leave a legend for the prime, so i can update the prime rate, update the data, and have a rolling repricing schedule - mid month and end of month.

I had a pretty decent if statement that added my prime rate cell, but when i add the weighted average and the max 2% threshold over the current rate, i am stuck mentally.
This formula gave me the average of all the spread rates (UNWEIGHTED) with a date range.
Clearly i just summed if the date was less than 9/15 it added the spread column, then divided by the count of anything less than 9/15 then added the prime rate i had inserted into B2.

=((SUMIF('Monthly Data'!F:F,"<9/15/2022",'Monthly Data'!M:M))/(COUNTIF('Monthly Data'!F:F,"<9/15/2022")))+B2

Suggestions?

 

[Cole Parker]

Using a sample data set below:
I need to get a weighted average for adjusting rates in the future.
The rate will be tethered to Cell B2 which is NY Prime + the spread column, with a cap of max increase of 2% over their CURRENT rate, but i need a weighted average of the spread by the column E, Current Balance... i will leave a legend for the prime, so i can update the prime rate, update the data, and have a rolling repricing schedule - mid month and end of month.

I had a pretty decent if statement that added my prime rate cell, but when i add the weighted average and the max 2% threshold over the current rate, i am stuck mentally.
This formula gave me the average of all the spread rates (UNWEIGHTED) with a date range.
Clearly i just summed if the date was less than 9/15 it added the spread column, then divided by the count of anything less than 9/15 then added the prime rate i had inserted into B2.

=((SUMIF('Monthly Data'!F:F,"<9/15/2022",'Monthly Data'!M:M))/(COUNTIF('Monthly Data'!F:F,"<9/15/2022")))+B2

Suggestions?


View attachment 73503

=SUMPRODUCT('Monthly Data'!M2:M147,'Monthly Data'!E2:E147)/SUM('Monthly Data'!E2:E147)

Above gets me my average spread, but it does not filter dates. after that formula i can just +B2 and get my average for the period if i only copied data from 9/8-9/15 for example.
if my data was to 9/30 this will pull all ranges rather than my mid month check.

 

[Cole Parker]

Bumping

 

[Cole Parker]

=SUMPRODUCT(IF('Monthly Data'!F2:F147<=DATE(2022,9,15),'Monthly Data'!M2:M147*'Monthly Data'!E2:E147))/SUMIF('Monthly Data'!F2:F147,"<="&DATE(2022,9,15),'Monthly Data'!E2:E147)


I have the weighted average for the range, including a less than/equal to date. I can adjust the ranges of columns as needed. Need to include a piece to the formula that will set a limit of 2% as a max up or down movement.