Y yaelcohen Member Jun 28, 2016 #1 Hello I am asking for help, I need to calculate the average for each firm-year, while the avearge is the last 5 profit that each firm has. If there is not 5 years of data please do not calculate it. I tried to calculate by myself but with wrong answer Pls advice Attachments Copy of Question 30.xlsb 9.1 KB · Views: 15
Hello I am asking for help, I need to calculate the average for each firm-year, while the avearge is the last 5 profit that each firm has. If there is not 5 years of data please do not calculate it. I tried to calculate by myself but with wrong answer Pls advice
J Jake Collins Active Member Jun 28, 2016 #2 Dear Yaelcohen This array formula works =IF(AND(SUM(1*($C$3:$C$35=$C3)*($D$3:$D$35<=$D3))>=5),SUMPRODUCT($E$3:$E$35,($C$3:$C$35=$C3)*($D$3:$D$35*($C$3:$C$35=$C3)>=LARGE($D$3:$D$35*($C$3:$C$35=$C3)*($D$3:$D$35<=$D3),5)),1*($D$3:$D$35<=$D3))/5,"")
Dear Yaelcohen This array formula works =IF(AND(SUM(1*($C$3:$C$35=$C3)*($D$3:$D$35<=$D3))>=5),SUMPRODUCT($E$3:$E$35,($C$3:$C$35=$C3)*($D$3:$D$35*($C$3:$C$35=$C3)>=LARGE($D$3:$D$35*($C$3:$C$35=$C3)*($D$3:$D$35<=$D3),5)),1*($D$3:$D$35<=$D3))/5,"")
B bosco_yip Excel Ninja Jun 28, 2016 #3 G3 : =IF(SUMPRODUCT(D3:D6-D4:D7)=4,AVERAGE(E3:E7),"") Copy down Regards Bosco
