Average Value Errors

dana y

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? 

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

 

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.