Hello. I inherited a spreadsheet that is very complex. It has a section with the dollar cost of invoices for a specific trade by month. Then it has a section that calculations the average by quarter (Q1, Q2, etc.) I can't copy the spreadhseet because it is confidential but let me see if I can explain the spreadsheet and hopefully someone can explain what the formula is doing (step by step)

=((SUMPRODUCT(($D$5:$BL$5=BR$5)*($D$6:$BL$6=CHOOSE(BR$5,1,4,7,10))*($D$7:$BL$7=BR$7)*$D214:$BL214)*SUMPRODUCT(($D$5:$BL$5=BR$5)*($D$6:$BL$6=CHOOSE(BR$5,1,4,7,10))*($D$7:$BL$7=BR$7)*$D83:$BL83))+(SUMPRODUCT(($D$5:$BL$5=BR$5)*($D$6:$BL$6=CHOOSE(BR$5,2,5,8,11))*($D$7:$BL$7=BR$7)*$D214:$BL214)*SUMPRODUCT(($D$5:$BL$5=BR$5)*($D$6:$BL$6=CHOOSE(BR$5,2,5,8,11))*($D$7:$BL$7=BR$7)*$D83:$BL83))+(SUMPRODUCT(($D$5:$BL$5=BR$5)*($D$6:$BL$6=CHOOSE(BR$5,3,6,9,12))*($D$7:$BL$7=BR$7)*$D214:$BL214)*SUMPRODUCT(($D$5:$BL$5=BR$5)*($D$6:$BL$6=CHOOSE(BR$5,3,6,9,12))*($D$7:$BL$7=BR$7)*$D83:$BL83)))/SUMIFS($D83:$BL83,$D$5:$BL$5,BR$5,$D$7:$BL$7,BR$7)

D5:BL5 identifies the quarter for each month of data (e.g Jan= Q1). BR5 is Q1. D6:BL6 has the name of the months. I'm not sure what that "choose" formula is doing. D7:BL7 identifies the year for each month of data. BR7 is 2011. Row 214 has the severity numbers (the cost). Row 83 has the number of invoices (I guess to do a weighted average of the severity for the quarter). Those are all the variables. Hopefully someone can help.

I'm also trying to figure out why some of the quarter numbers are showing up as Div/0 when there is only recent information. If anyone can think of an easier formula to sumif for Q1 and year, and then do a weighted average of cost, I'm all ears.