Average Collect Formula Not Calculating Correctly.

2»

Answers

  • Katye Reed
    Katye Reed ✭✭✭✭✭

    Everything looks good!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Great. So we have narrowed it down to this range.

    {FY 21 340b Activity Tracking Days to Completion}

    This is the data you are actually collecting for the AVG function. Exactly how is this data being populated?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Katye Reed
    Katye Reed ✭✭✭✭✭

    Thanks - this is being calculated with the following formula:

    =IFERROR(NETWORKDAYS(Start@row, [Actual End Date]@row), "")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Well that is producing numerical values, so it should be working correctly. Let's try expanding our COUNTIFS...

    =COUNTIFS({FY 21 340b Activity Tracking Activity Frequency}, "Monthly", {FY 21 340b Activity Tracking Location}, "APD", {FY 21 340b Activity Tracking Parent Level}, 3)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Katye Reed
    Katye Reed ✭✭✭✭✭

    That worked - it's calculating correctly.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. Then what is the AVG/COLLECT generating vs what should it be?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Katye Reed
    Katye Reed ✭✭✭✭✭

    It's calculating a number, -27 days which is correct for the MHMH location annual activity type. But it is calculating this for all activity types and locations which is incorrect. Would it help if I shared the metric sheet with you?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Hmm... It seems like it is something that is lingering on the back-end.


    If this does not work, I am going to have to suggest reaching out to support.

    Remove incorrect formula(s)

    Log out

    Clear cookies and cache

    Log back in

    Re-enter one of the formulas to see if it is working correctly


    Does that help any?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Katye Reed
    Katye Reed ✭✭✭✭✭

    I appreciate your assistance. No - it is still displaying incorrectly. I will submit a ticket.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Sorry I couldn't be of more help. Feel free to revisit this thread with the solution.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Katye Reed
    Katye Reed ✭✭✭✭✭
    edited 07/14/20

    I reached out to support and it appears I had an extra parenthesis which caused the formula to exclude on of the criteria.

    Correct Formula: =IFERROR(AVG(COLLECT({FY 21 340b Activity Tracking Days to Completion}, {FY 21 340b Activity Tracking Parent Level}, 3, {FY 21 340b Activity Tracking Activity Frequency}, "Monthly", {FY 21 340b Activity Tracking Location}, "APD")), "")

    Original Formula: =IFERROR(AVG(COLLECT({FY 21 340b Activity Tracking Days to Completion}, {FY 21 340b Activity Tracking Parent Level}, "3"), {FY 21 340b Activity Tracking Activity Frequency}, "Monthly", {FY 21 340b Activity Tracking Location}, "APD"), "")


    Thanks so much for all of your help.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ugh. That's usually one of the first things I look for too. Sorry for missing that. Glad you were able to get it working.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!