The Average Number of Monthly Projects

Options

I want to be able to have a formula that pulls the average number of projects we complete monthly for our sheet summary. I have a date column for "Date Completed". Can anyone help with what the formula would look like?

Tags:

• ✭✭✭✭✭✭
Options

If there is a cell within the range that does not have a date in it such as a blank cell or text, then there will be no data for the YEAR or MONTH function which will throw an error. So we use an IFERROR to turn that error into a zero which obviously does not equal 1 or 2020 or whatever other year or month number you are searching for. This allows the formula to continue running and ensures non-date values within the range will not skew the incoming data.

• Options

Thank you, I was able to get this to work! I'm still a little bit confused by the use of the IFERROR function, would you be able to explain why the 0 needs to be there?

• ✭✭✭✭✭✭
Options

If there is a cell within the range that does not have a date in it such as a blank cell or text, then there will be no data for the YEAR or MONTH function which will throw an error. So we use an IFERROR to turn that error into a zero which obviously does not equal 1 or 2020 or whatever other year or month number you are searching for. This allows the formula to continue running and ensures non-date values within the range will not skew the incoming data.

• Options

Hi Paul,

Is there a modification that can be made to that same type of formula above to enable it to repeat for each consecutive month by dragging?

example:

.....(MONTH(@cell), 0) = 1,.....

.....(MONTH(@cell), 0) = 2,.....

.....(MONTH(@cell), 0) = 3,.....

Even when I have two rows above in the same pattern, it doesn't repeat.

Thanks, Jason

• ✭✭✭✭✭✭
edited 04/02/20
Options

@Jason Egles Enter the number into a separate column and then use a cell reference.

Formula Column...............Month Number

..............f........................................1

..............f........................................2

..............f........................................3

Then the formula would be adjusted to say:

.....(MONTH(@cell), 0) = [Month Number]@row,.....

• Options

Awesome! That's exactly what I needed. Thanks a lot @Paul Newcome

• ✭✭✭✭✭✭
Options

@Jason Egles Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!