This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I have a column of values that are the result of an IF equation and I want to take the average of these values excluding blank cells.
The IF function used is:
=IF(D2="C","4",IF(D2="C+","5",IF(D2="B","6",IF(D2="B+","7",IF(D2="A-","8",IF(D2="A","9",IF(D2="A+","10","")))))))
So now I have a column made up of values ranging from 4-10 and some blank cells and I want to take the average of this column using
=AVERAGEIF(E2:E42,"<>0")
This resulted in a #DIV/0! error so I tried using
=IF(COUNT(E2:E42)>0,AVERAGE(E2:E42),"")
which resulted in a blank cell meaning the IF statement was false.
Why isn't the average function recognizing all the values in column E? I made sure all the cells were formatted as numbers. I'm assuming this problem is because all the values in column E are the results of an equation but I don't know how to get around this.
Hoping someone could help!
Thanks
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
Remove quotation marks around number. Hence, use below formula (Average doesn't operate on Text numbers)
=IF(D2="C",4,IF(D2="C+",5,IF(D2="B",6,IF(D2="B+",7,IF(D2="A-",8,IF(D2="A",9,IF(D2="A+",10,"")))))))
Awsome Thanks!
This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Comments have been turned off. Learn more