How to Calculate Date Specific Average?

Ann Foley

I'll be updating data once a month and want to get the average of that data. Currently I'm using =SUM(CHILDREN()) / 12 but that messes up the average because it's dividing January's data by twelve. It should be sum of the children / 2 months (for the average as of February), and then sum of the children / 3 months (for the average as of March), and so on. I'm hoping that I don't need to manually update the formula monthly because it applies to a ton of parent rows!

Appreciate any help!!

Paul Newcome

Ah. My mistake. I see what you are doing now. Sorry about that. If you are putting the formula in each of the columns to average them each, we can just remove the column reference.

=SUM(CHILDREN()) / COUNTIFS(CHILDREN(), NOT(ISBLANK(@cell)))

You could also use

=AVERAGEIFS(CHILDREN(), ISNUMBER(@cell))

Paul Newcome

How do you have each of the Parent rows filled in? Do you just have "January" or do you have an actual date somewhere? Are all of the parent months on the same level of hierarchy?

I can think of a few different ways to do this, but the best one will depend on your actual setup.

Can you provide a screenshot of how the sheet currently looks with sensitive/confidential data hidden, removed, or replaced with "dummy data" as needed?

Ann Foley

I have January noted- see below

Paul Newcome

Ok. So to be clear...

The formula is going in the top row where "Average (2020)" is in the Property column?

Will there be any data in the Feb row while it is still Jan? March while it is still Feb, so on and so forth?

If the January data is all that will be entered until February and March will remain blank until March, so on and so forth, you can use a formula such as

=SUM(CHILDREN()) / COUNTIFS(CHILDREN([GSS ITRec]), NOT(ISBLANK(@cell)))

What this does is counts how many rows are filled in in the [GSS ITRec] column and divides by that. There are some other options if that doesn't work for you. Let me know.

Ann Foley

Yes, the formula will go where "Average 2020" is noted.

No, there will not be any data in Feb until we reach that month, and so on and so forth. So there will be blank months until we get to the end of December

That didn't work - I think perhaps becuase I have a bunch of different properties so it's not just dividing by the GSS ITRec column? It should only divide by the GSSITRec for that Parent and Children cells. See below for visual

Paul Newcome

Ah. My mistake. I see what you are doing now. Sorry about that. If you are putting the formula in each of the columns to average them each, we can just remove the column reference.

=SUM(CHILDREN()) / COUNTIFS(CHILDREN(), NOT(ISBLANK(@cell)))

You could also use

=AVERAGEIFS(CHILDREN(), ISNUMBER(@cell))

Ann Foley

Amazing, thank you so much!!!

Paul Newcome

Happy to help! 👍️