Need Help with a formula

Jared123456789

I need a formula that will average data enter every day. For example I enter Paul 35 resolves on 1/18/2024, Paul 40 resolves 1/19/2024, and Paul 55 Resolves on 1/20/2024. So I would want it to calculate 35+40+55 divided by three days of info submitted.

Kelly Moore

Hey @Jared123456789

An AVG/COLLECT combination should give you the results you need, depending on how your sheet is structured. Is your formula in the same sheet?

Here's a rough outline of the formula, assuming the formula is on the same sheet as the data:

=AVG(COLLECT([Daily data column]:[Daily data column], [Date column]:[Date column], AND(ISDATE(@cell), @cell⇐ [Date column]@row), [Daily data column]:[Daily data column], @cell<>"", [Name column]:[Name column], [Name column]@row))

You will need to replace my column name placeholders with the actual column names of your sheet

Does this work for you? If this formula isn't working, is it possible to share a screenshot of your sheet? Columns we need to see include Date, Person's name, and the daily entry, and the formula column.
Kelly

jmyzk_cloudsmart_jp

Hi @Jared123456789

Here is a solution.

https://app.smartsheet.com/b/publish?EQBCT=b6cd9f9393854915b0d247fba3350c09

Jared123456789

Alex Resolves.png

Og Sheet.png

Reference.png

Jared123456789

I have posted pictures of what sheets I am using the OG sheet is where I used references to Alex resolves, I would like to average the resolves by week in the Alex sheet

Kelly Moore

Hey @Jared123456789

Try this. Since this uses cross sheet references you will need to create the references rather than simply copy pasting the formula

Paragon Resolves:

=AVG(COLLECT({OG Sheet Paragon Resolves column}, {OG Sheet Week Number column}, [Week Number]@row, {OG Sheet Name column}, "Alex"))

Does this work for you? The SIMS Resolves should follow the same structure except the initial range is the SIMS Resolve column

Kelly

Jared123456789

Now I am getting this :(

Jared123456789

Kelly Moore

Hey @Jared123456789

With the exception of the zero entries producing the Divide by Zero error, are all of your other entries correct? Dividing by zero does in fact result in that error message.

After ensuring the formula is otherwise working as expected, you can get rid of the error message by wrapping the entire formula, parentheses and all, in an IFERROR function. This function tells smartsheet what to do if an error is encountered. Since it will mask most errors, one always ensures the formula works properly before adding the IFERROR.

I will assume you want a zero to appear in place of the error message. Let me know if you would rather have a blank cell.

=IFERROR(AVG(COLLECT({OG Sheet Paragon Resolves column}, {OG Sheet Week Number column}, [Week Number]@row, {OG Sheet Name column}, "Alex")),0)

Does this work for you? Please be sure to @mention me with any replies to make sure I receive an email.

Kelly