How to calculate daily average

[emmr]

Hello Everyone,
i've a sample file with example & its result for clear understanding.

how i can do it with formula for faster result from large data.

based to column D (Y/N), if yes then find the average of a particular date, if No then take the Yes to get the average of a particular date.
if the NO changed to YES then it should include automatically to get the average.

thanks in advance

[Shareez Saleem]

Please Login or Register  to view this content.

[Glenn Kennedy]

E2:
=IF(D2="No",0,AVERAGEIFS(C:C,A:A,A2,D:D,"Yes"))

and in G2:
=SUMIFS(E:E,B:B,B2,A:A,"<="&A2)/F2

delivers your expected results... But check that your expected results REALLY are what you want. The logic behind them seems very strange to me.

[samba_ravi]

E2=SUMIFS(C$2:C$12,D$2:D$12,"Yes",A$2:A$12,A2)/COUNTIFS(D$2:D$12,"Yes",A$2:A$12,A2)*(D2="Yes")
Try above formula, copy and paste towards down

[emmr]

thanks a lot everyone it working

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It would also be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.