Hi, i'd appreciate if someone can assist me in solving this #DIV/0! error, been stuck on it for quite some time.
I am trying use cell D1 (9/7/2016) to get the sum of the prices since the beginning of the month & same year up to the date then divide by the number of days which don't have $0.00 values within that month.
In the sample below, I should be dividing $157.06 by 5. However my avg keeps giving me the DIV error because its dividing 157.06 by 0.
The formula for my avg is as follows: =SUMPRODUCT((B2:B14)*(MONTH(A2:A14)=MONTH(E1))*(YEAR(A2:A14)=YEAR(E1))*(DAY(A2:A14)<=DAY($E$1)))/(COUNTIFS(A2:A14,SUMPRODUCT(--(MONTH(A2:A14)=MONTH(E1))*(YEAR(A2:A14)=YEAR(E1))),B2:B14,">0"))
Thank you.Please Login or Register to view this content.
Bookmarks