Average Formula Issue

ELaliberte
edited 12/09/19 in Smartsheet Basics

In my project, I'm trying to find the average of a few cells (in some cases it is only one cell), but I keep getting a #DIVIDE BY ZERO error message. Do you know why?

Capture2.PNG

Capture3.PNG

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi,

    Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help. 

    I hope this helps you!

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Sure. I'm trying to get the overall average for an objective. In the first screenshot below, the 'Status' column has drop-down options to select. Depending on the status, I have a formula to populate the output level 'Status Color' column to be certain RYGB dot colors. I'm using this formula for the Status color:

    =IF(Status30 = "On Schedule", "Green", IF(Status30 = "Complete", "Blue", IF(Status30 = "Needs Organizational Attention", "Red", IF(Status30 = "Not Started", "", IF(Status30 = "Needs Management Attention", "Yellow")))))

    The output level 'Status Converter' column then populates a certain number based on the colors. Formula here:

    =IF([Status Color]30 = "Red", 1, IF([Status Color]30 = "Yellow", 2, IF([Status Color]30 = "Green", 3, IF([Status Color]30 = "Blue", "4", IF([Status Color]30 = "", "0")))))

    The number ^this formula pulls is then used for the average for the outcome level (i.e. row29). I am trying to find the separate average for each outcome row (i.e.,row 29, 31, 33, & 35) and then from there use the outcome level average to find the overall average for the Objective (row 28). This is where I get the error message for finding the average of the outcome level.

     

    Capture5.PNG

    Capture6.PNG

    Capture7.PNG

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 11/30/18

    Are your numbers being populated by a formula? If so, you will need a VALUE function to pull the ACTUAL number, otherwise there are no numbers for the AVG function to work with which would produce a #DIVIDE BY ZERO error.

     

    You could have some formatting in place for the column and whatnot, but generally speaking from my own personal experience, if a number is in a cell as a text string, it will be on the left side of the cell. If it is an actual number, it is on the right side of the cell (unless you format the column to show all data in a specific area of the cell).

     

    EDIT: It looks like I keep typing at the same time as other people this morning. Haha. Wrap your second formula in a VALUE function like so

     

    =VALUE(IF(...................))

     

    You can remove the quotes around the numbers in your IF statement which may correct the issue as well.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Hi There, sorry to pipe in but the issue is that the formula you are using to populate the numbers is using quotations around the 4 and 0.

    =IF([Status Color]30 = "Red", 1, IF([Status Color]30 = "Yellow", 2, IF([Status Color]30 = "Green", 3, IF([Status Color]30 = "Blue", "4", IF([Status Color]30 = "", "0")))))

    Using quotations around the 4 makes the output a text based output and the average formula only works with numbers. Simply remove the quotes from around the 4 in the formula like this... 

    =IF([Status Color]30 = "Red", 1, IF([Status Color]30 = "Yellow", 2, IF([Status Color]30 = "Green", 3, IF([Status Color]30 = "Blue", 4, IF([Status Color]30 = "", 0)))))

    And your average will work. 

    Only use quotes if you want your output to be a text answer. 

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Remove the quotes from the 0 as well. 

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Thanks Mike. I wasn't 100% positive on the quotes when I said it as an option as I've never actually tested it. I was just taking an edumacated guess there, so the confirmation was good to have. Haha.

  • Adding the VALUE worked. Thank you!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Haha. I totally missed that you said that. I was aiming for a conveying lesson there.

    "Anything that is put in quotes is considered as text, even numbers."

    You were correct! :)