# Average Value Errors

Options
edited 12/09/19

Hello,

Was hoping someone could see what i am doing wrong here; i am attempting to take the value of two cells and average them.  Both are percentages.

i use the formula =AVG([average calc]33, [average calc]34) and i get the "DIVIDE BY ZERO" error.  From what i read this may be due to the particular column is a dropdown list ..

so i created a new column (just text number type)  .. sent the results of the two columns there and attempted to use the formula again; still divide by zero error.

i then added the word value to the front of my formula; to help out with the text vs number deal - still error. i created yet another column that is not a dropdown list .. used the average function with no luck; then  added the word value there also with no luck.  anyone see what i am missing?

• ✭✭✭✭✭✭
Options

Try this...

=((COUNTIFS(Status34:Status42, "Not Applicable") + COUNTIFS(Status34:Status 42, "Complete") / COUNT(Status34:Status42)) * 100 + "%"

• Options

thank you but no luck   that is an unparseable error

• ✭✭✭✭✭✭
Options

Hi Dana,

I think Paul had one parenthesis too much at the beginning of the formula.

Try this.

=(COUNTIFS(Status34:Status42, "Not Applicable") + COUNTIFS(Status34:Status 42, "Complete") / COUNT(Status34:Status42)) * 100 + "%"

I hope this helps you!

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.

• Options

no thank you though, same error

• ✭✭✭✭✭✭
edited 10/25/18
Options

I actually had one too few after the second COUNTIFS.

=((COUNTIFS(Status34:Status42, "Not Applicable") + COUNTIFS(Status34:Status 42, "Complete")) / COUNT(Status34:Status42)) * 100 + "%"

After the second COUNTIFS.

I have one set of parenthesis around the COUNTIFS so they get added FIRST. I then have the second set around the COUNTIFS and the COUNT just to make sure the sum of the first gets divided by the COUNT before multiplying by 100.

• ✭✭✭✭✭✭
Options

Happy to "try to" help!

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

Ah, I see it now. I noticed something was off.

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

thanks much

i've been working on it and got them to a point where it works just fine within the sections ., .. but i am still unable to get that average into my project heading ..

i have tried all of the formulas you guys suggested  (avg error 5) .. and they work for the lower section ; but the total section just doesn't want to cooperate

you can see in avg error 6 i simply use the avg formula with no luck even though i have used the VALUE within each cell to ensure they are numbers ..

• ✭✭✭✭✭✭
Options

@Andree

Thanks for catching that. It's been a loooooong day.

@dana

That's because when you are referencing the other cells in your overall formula, you are referencing a text string. You will have to reference the NUMBERS themselves.

My suggestion would be to add a helper column (we'll call it Helper for this example. For each of the subsections in the helper column, use the following:

=((COUNTIFS(Status34:Status42, "Not Applicable") + COUNTIFS(Status34:Status 42, "Complete")) / COUNT(Status34:Status42)) * 100

To display the result in your Status column, simply enter

=Helper@row + "%"

In your overall calculation you can use the AVG function and reference the numbers in the helper column and then just put the formula immediately above into your header row.

Sometimes it's easier to just use some helper columns to do the calculations with the numbers then pull that value and add the text in the cell you want to display it in. You can then hide the helper column and no one will know (except for us Hahaha).

• Options

thanks again everyone

i tried a form of that initially .. i created a new column to just simply do the calc and repeat it back int the status column .. and i could not get it to work .. hence why i added the "VALUE"

i am trying your suggestion but again i am not getting anywhere ; still getting the divide by zero error ..

• Options

it's the percentage that is causing all my issues!!

thanks guys so much for helping me work through this; so much appreciated as it was driving me crazy (er)

• ✭✭✭✭✭✭
Options

Happy to help! Glad you got it figured out.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!