Average Formula not Ignoring Blanks

Daniel.W

Hello! I am having problems with the below formula (its a big one). I am trying to avoid creating 9 helper columns if possible. The below formula is including blank cells as 0s when averaging. I have tried a variety of combinations of OR, <>0, etc and cannot find a way to skip the blanks. Some of what I have tried will skip a blank but then return an empty average essentially skipping the whole formula for one blank. Please help!

=IFERROR(AVG(VALUE(IF(CONTAINS("4", Shirt@row), "4", IF(CONTAINS("3", Shirt@row), "3", IF(CONTAINS("2", Shirt@row), "2", IF(CONTAINS("1", Shirt@row), "1", ""))))), VALUE(IF(CONTAINS("4", Walked@row), "4", IF(CONTAINS("3", Walked@row), "3", IF(CONTAINS("2", Walked@row), "2", IF(CONTAINS("1", Walked@row), "1", ""))))), VALUE(IF(CONTAINS("4", Greet@row), "4", IF(CONTAINS("3", Greet@row), "3", IF(CONTAINS("2", Greet@row), "2", IF(CONTAINS("1", Greet@row), "1", ""))))), VALUE(IF(CONTAINS("4", Friendly@row), "4", IF(CONTAINS("3", Friendly@row), "3", IF(CONTAINS("2", Friendly@row), "2", IF(CONTAINS("1", Friendly@row), "1", ""))))), VALUE(IF(CONTAINS("4", [Issues Log]@row), "4", IF(CONTAINS("3", [Issues Log]@row), "3", IF(CONTAINS("2", [Issues Log]@row), "2", IF(CONTAINS("1", [Issues Log]@row), "1", ""))))), VALUE(IF(CONTAINS("4", [Trouble Tickets]@row), "4", IF(CONTAINS("3", [Trouble Tickets]@row), "3", IF(CONTAINS("2", [Trouble Tickets]@row), "2", IF(CONTAINS("1", [Trouble Tickets]@row), "1", ""))))), VALUE(IF(CONTAINS("4", Concluded@row), "4", IF(CONTAINS("3", Concluded@row), "3", IF(CONTAINS("2", Concluded@row), "2", IF(CONTAINS("1", Concluded@row), "1", ""))))), VALUE(IF(CONTAINS("4", Phonetool@row), "4", IF(CONTAINS("3", Phonetool@row), "3", IF(CONTAINS("2", Phonetool@row), "2", IF(CONTAINS("1", Phonetool@row), "1", ""))))), VALUE(IF(CONTAINS("4", [Welcome Comm]@row), "4", IF(CONTAINS("3", [Welcome Comm]@row), "3", IF(CONTAINS("2", [Welcome Comm]@row), "2", IF(CONTAINS("1", [Welcome Comm]@row), "1", "")))))), "")

Hollie Green

Have you tried a sumif/countif

Daniel.W

I haven't because not every column is potentially going to be completed (hence the reason for the potential blanks) so I didn't want to do a static sumif then divide by total as the number of categories could change for the averages. Sometimes 5 sometimes 7 etc.

Kleerfyre

From what I what I can tell, your formula is going to count them all into the average every time due you having them all listed in the formula and due to you having the formula populating something, even a "space". Try first removing the " " from every part of the formula. The reason for this is because that is the false part of the formula and you don't have to have it put that because if the statement is false, it will just not do anything to the cell. See if that helps any.