Average Formula Issue

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)

Find more posts tagged with

Accepted answers

All comments

Nasir@EBC

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)

Andrée Starå

Hi @Hoi Man Cheung,

To add to Nasir's excellent advice.

Here's more information:

https://help.smartsheet.com/function/avg

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Hoi Man Cheung

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

Andrée Starå

@Hoi Man Cheung Strange!

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

Hoi Man Cheung

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

Andrée Starå

@Hoi Man Cheung

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)

Paul Newcome

@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)

Andrée Starå

@Hoi Man Cheung, I saw that Paul answered already!

@Paul Newcome, Nice catch!

Let me know if I can help with anything else!

Best,

Andrée

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.