# Average Formula Issue

Options

Hi there, i am trying to average cells that were built up by formulas.

So basically the Column 1-7 are the scores when the task is done , it will turns into 100 when the task is complete, and where the right handside column would need to average the score of column 1-7.

When I tried to average , it turns out "UNPARSEABLE" ; Below is the formula I tried to create.

(I also tried Average ISERROR, if also didn't work)

• ✭✭✭
Options

Hi Hoi

In smartsheet the word Average is not defined as a formula to calculate the averages. Instead, try the following:

=Avg([rangestart]:[rangefinish])

Best :)

Nasir (Zealvert)

• ✭✭✭✭✭✭
Options

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

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.

• Options

Hi , I am Afraid even I changed to AVG , it still shows "divide by ZERO error".

• ✭✭✭✭✭✭
Options

@Hoi Man Cheung Strange!

Where are the linked values coming from and what format are they?

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.

• Options

The Values in column 1-7 are formula as I mentioned, please refer to the below which I capture the formula contains.

• ✭✭✭✭✭✭
Options

i'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

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.

• ✭✭✭✭✭✭
edited 02/21/20
Options

@Hoi Man Cheung Remove the quotes from around the numbers in your formula.

=IF(VLOOKUP(Marsha8, {ERS 2020 Photo Wave 1 LAND-IT Report Range 1}, 13, false) = "complete", 100, 0)

You will still get the #DIVIDE BY ZERO error until there is at least one value greater than zero within the row. To avoid this, you can wrap your AVG formula in an IFERROR to replace the error message with the number 0.

=IFERROR(AVG([Milestone 1 - Submit Property Coaches]8:[Ready To Go-Live]8), 0)

• ✭✭✭✭✭✭
Options

@Paul Newcome, Nice catch!

Let me know if I can help with anything else!

Best,

Andrée

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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!