Example sheet here.
Basically, I want to find out how many days have had their values entered, then average the totals across the number of days. If a day's value hasn't been entered it will be blank, but the value can be 0.
What I've been using is this:
=IF(isnumber(G2), sum(A12:G12)/7,
if(isnumber(F2), sum(A12:F12)/6,
if(isnumber(E2), sum(A12:E12)/5,
if(isnumber(D2), sum(A12:D12)/4,
if(isnumber(C2), sum(A12:C12)/3,
if(isnumber(B2), sum(A12:B12)/2,
A12))))))
Which seems like a cumbersome way to do it, plus the number of days tracked will be variable in the near future. I know I could replace it with IFS()
and isblank()
, but is there a more scalable option that doesn't require so much manual entry?
Best Answer