Forum Discussion
Need help with this error #DIV/0!
Hi,
I am using AVERAGEIF to get a specific average in a particular group but I kept getting the error #DIV/0! using the excel application. But each time I use the excel browser, I get the outcome with no issue. Can anyone help me with this?
This photo is from browser.
This is from the excel application.
In column S you have texts like "$100", not numbers formatted as currency. Select column S, Ctrl+H and replace $ on nothing. It works. After that you may apply currency format to that column.
The #DIV/0! error in Excel occurs when a formula tries to divide by zero or by an empty cell. Since you are using the AVERAGEIF function, this error likely happens if the range you are trying to average either has no valid data or all the values in the range are 0 or blank.
How to correct a #DIV/0! error
The DIV/0# error is simply telling you that there are no matches to your criterion. That may simply be a fact or there may be something wrong in your criterion range such as additional blanks or non-printing characters.
- Appreciate your response Peter. Thank you!
- Change your formula into the following one:
=AVERAGEIF(S2:S89,G2:G89,"Youth (<25)")
Have a try.- Thank you! It didn't work for me but it's fine. I think the file it self has a problem. Since the same functions works using different data files except that one file.
Quite strange. This formula shall work exactly the same way in Excel for Web and in Desktop, practically for any version of Excel Desktop. Why the latest doesn't recognize "Youth (<25)" in column G, assuming you did no changes in the file includes any copy/paste, is not clear.
Perhaps you may share the file to check, removing from it all sensitive information, actually keeping only columns G and S plus cell with the formula.
SergeiBaklan That's what I thought too. I have a feeling that the problem is just within the file cos I just reinstalled my 365 and it's still the same. I tried using the same function with different data files, and it works fine.
I would appreciate it If you could look at it too to see the possible issue. Thank you!In column S you have texts like "$100", not numbers formatted as currency. Select column S, Ctrl+H and replace $ on nothing. It works. After that you may apply currency format to that column.
