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?

avg error 2.PNG

avg error 3.PNG

avg error 4.PNG

avg error1.PNG

Find more posts tagged with

Formulas

Solution Center

Community Platform

Cell linking

Comments

Paul Newcome

Try this...

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

dana y

thank you but no luck that is an unparseable error

Andrée Starå

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

dana y

no thank you though, same error

Paul Newcome

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.

Andrée Starå

Happy to "try to" help!

Andrée Starå

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

dana y

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

avg error 5.PNG

avg error 6.PNG

Paul Newcome

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

dana y

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

dana y

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)

Paul Newcome

Happy to help! Glad you got it figured out.

Sign in to join the conversation!

Quick Links

Trending discussions

Looking for assistance with a formula
Hi all, I am new to formulas and functions in Smartsheet and have a large sheet that I would like to summarize using an "average" formula/function. I need to include at least 2 separate criteria to filter correctly and I am having trouble combining the avg and collect functions as suggested in the functions list. I have…
Need help in formula function
I'm not able to get this formula to function in Smartsheet. The material ordered date is 9/3/2025. Am I missing something to get the date rage to populate? =TEXT([Material Ordered Date]@row +28,"MM/DD/YYYY")&"-"&TEXT([Material Ordered Date]@row +42, "MM/DD/YYYY")
I am trying to find a value in a table based on 2 criteria.
I am trying to find a value in a table based on 2 criteria. One of the values is dynamic from a drop down menu, while the other is static value in a column. The first value is found in column "Building type" and the other is found in Resource type. The table I am referencing is also shown. The result would be shown in the…