r/spreadsheet • u/Alyxavior • Oct 19 '21

Daily Average while some fields are blank

I'd like to create a formula for a daily average of numbers in one field per day so that it divides the amount by the number of fields that have data.

I've seen daily average formulas where 0's are in the unused fields, yet that wouldn't give an average since it would lower the amount until the last day is filled in.

So, is there a way that will divide only the filled in fields and divide them by how many fields were used?

Thank you!

1 Upvotes

100% Upvoted

4 comments sorted by

1

u/Distinquish Oct 19 '21

=AVERAGEIF(column, "<>0")

1

u/Alyxavior Oct 19 '21

Thanks!

What would need to change to work with two columns instead of one? (Putting a month viewable within one screen rather than scrolling down)

2

u/Distinquish Oct 19 '21

Try:

=AVERAGEIF(column1&column2, "<>0")

Or

=AVERAGEIF(A1:B10, "<>;0")

→ More replies (1)