0

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?

1 Answer 1

1

=AVERAGE(INDIRECT("A12:"&ADDRESS(12, COUNTA(A2:2), 4)))

0

2
  • That did it! I have another similar sheet that has analytic data in the cells to the right of the entry fields. Is there any issue using this? =AVERAGE(INDIRECT("A13:"&ADDRESS(13, COUNTA(A2:2)-1, 4))) Specifically, COUNTA(A2:2)-1 to prevent that field from being counted towards the average.
    – Nick
    Apr 29, 2019 at 17:23
  • @Nick yeah, that's it if you wanna exclude H column: i.stack.imgur.com/DCgeA.png
    – user0
    Apr 29, 2019 at 17:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.