Array Formula: Weighted Avg with Min/Max criteria

**ExcelNewbeee** · 06-28-2013, 06:45 PM

Hi,

In cell $I$4 of the attached file, I need an array formula that will sum the calculated weighted averages of the total number of days from a given period that fall into each calander year and multiply that by the rate for each year. I may not have explained this correctectly, but basically I need a formula in cell $I$4 that returns a value that equals cell $I$6.

Any help would be greatly appreciated!

Thanks.

**DonkeyOte** · 06-30-2013, 02:48 AM

One possibility

Formula:



=SUM(IFERROR(DATE(1900,1,IF($D$2:$H$2<$B$4,$D$2:$H$2,$B$4)-IF($D$1:$H$1-1>$A$4,$D$1:$H$1-1,$A$4)),0)/($B$4-$A$4)*$D$4:$H$4)

confirmed with CTRL + SHIFT + ENTER