Forum Discussion
Removing or hiding errors
Hi there,
Can anyone help please as this is driving me nuts! If so, could you take a look at the screenshots below - I have included the formulas for you to see.
I am creating a document for others to fill in and #div/0! is being displayed in any cell that is waiting for information. I have looked into this and have tried using the 'IFERROR' command but I'm obviously doing something wrong.
To explain:
Z8 returns the average score across 7 weeks and my formula for this is: =IFERROR(AVERAGE(S8:Y8),"")
Cell AA8 is connected to Z8 using the IFS formula and should return four different text options (well above, above, at, towards) depending on what the average score in Z8 turns out to be. My formula for this is: =IFERROR(IFS(Z8>=3.5,AA$3,Z8>=2.5,AA$4,Z8>=1.5,AA$5,Z8>=0,AA$6)," ")
The IFERROR formula works in cell Z8 (highlighted in yellow) as nothing is shown in the box and this is great. However, I then wanted cell AA8 to show nothing in response to this. Instead it is now showing the number four (linked to Z3).
I would like the 'average' cell to be blank when there are no numbers to average, and similarly, the 'statements' column (starting with AA8) should then be blank too.
If anyone could help I would be really grateful.
Thank you.
Showing 'average' cell
Showing 'statement' cell
To exclude errors you may use something like
=AVERAGEIFS(S8:Y8, S8:Y8, "<>#DIV/0!")Average in any case returns number. If nothing except errors then zero. To have empty string you may wrap it as
=IF( AVERAGEIFS(S8:Y8, S8:Y8, "<>#DIV/0!"), AVERAGEIFS(S8:Y8, S8:Y8, "<>#DIV/0!"), "" )
To exclude errors you may use something like
=AVERAGEIFS(S8:Y8, S8:Y8, "<>#DIV/0!")Average in any case returns number. If nothing except errors then zero. To have empty string you may wrap it as
=IF( AVERAGEIFS(S8:Y8, S8:Y8, "<>#DIV/0!"), AVERAGEIFS(S8:Y8, S8:Y8, "<>#DIV/0!"), "" )- Thank you so much for taking the time to reply - it's fixed!
=IF(Z8="","",IFS(Z8>=3.5,AA$3,Z8>=2.5,AA$4,Z8>=1.5,AA$5,Z8>=0,AA$6))
You can apply this formula in cell AA8.
- Thank you for taking the time to reply - it's fixed!
- Thank you for taking the time to reply - it's fixed!
