Average Value Errors
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?
Comments

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

thank you but no luck that is an unparseable error

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.

no thank you though, same error

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.

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.

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.

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 ..

Thanks for catching that. It's been a loooooong day.
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).

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 ..

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)

Happy to help! Glad you got it figured out.
Help Article Resources
Categories
Check out the Formula Handbook template!