# How to Calculate Date Specific Average?

Options
✭✭✭✭

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!!

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭
Options

I have January noted- see below

• ✭✭✭✭✭✭
edited 01/28/20
Options

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.

• ✭✭✭✭
Options

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

• ✭✭✭✭
Options

Amazing, thank you so much!!!

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!