0

I'm having an issue with a formula in my Google Sheet. I've searched the internet some to see if anyone's had the same problem and found a solution but it seems there's nothing so I'm hoping you all can help. Basically I have a bunch of cells which use the IFERROR() function to return the word "Blank" if the cell is an error, and there are 100+ cells where it works as expected, but for some reason I can't understand there are two rows where it's decided to return "0" instead of "Blank." The text in the formula is exactly the same as all the others, I've tried multiple times just copying and pasting one of the working cells in place of the broken ones, yet I get the same result. I've tried just deleting and manually rewriting the formula in the hopes it would reset itself but no luck there either. The strange thing is, when I'm editing the formula and it shows the little white popup with blue text that's meant to show you the output of the formula, it correctly shows as "BLANK". But upon hitting enter it just shows up as a 0 in the cell. And it's not just how it displays, other cells are reading it as a 0 instead of BLANK which creates problems. I'll attach a link to the document, but there's a whole lot going on so just ignore everything and scroll to Row 262, column K. The tables exist from K262 to AL289, and most of them are working properly, but R271, S271, R274, and S274 are the broken cells. You can see when the cells immediately underneath each respective cell (the ones they are referencing) contain errors, they return 0 instead of "BLANK", like all the other ones are doing.

4
  • HI, welcome to WebApps. Your spreadsheet requires permission to access. In order to have the greatest number of people read and consider your problem, I suggest that you share your spreadsheet so that anyone with the url can access it.
    – Tedinoz
    May 5 at 22:14
  • Sorry, I thought it was available for anyone with a link, I'll fix that
    – Soy Milk
    May 5 at 22:33
  • I had a quick look at the relevant formula in your spreadsheet. The formula relies on at least 80 (eighty) referenced cells and formulas. The spreadsheet as a whole is a extremely complex. My suggestion to you is to troubleshoot the specific formula by breaking down the results by referenced cell and identifying when the error value is created. Personally, I found the spreadsheet so convoluted that I couldn't justify spending more time trying to investigate your problem, let alone try to solve it.
    – Tedinoz
    May 6 at 2:53
  • Hi! it's not the error message that I'm worried about, those cells are supposed to return error messages. It's the ones above them that are meant to return the word "BLANK" when those cells are errors, and that happening isn't dependent on anything else in the document. The cells in question only reference the one cell to ask whether or not it's an error, which it is, and does not result accordingly. The issue is really just between those cells. Hope that makes sense.
    – Soy Milk
    May 6 at 5:06

1 Answer 1

-1

you can try one of the below formula 1.This will return 0 if error =iferror(value(mid($P$8,search(".",$P$8)-3,1)),"0")

2.This will give you a "blank call" if there is an error. =iferror(value(mid($P$8,search(".",$P$8)-3,1)),"")

Similarly, you can do this for other formulas too.

Let me know if you need more help with this.

thank you

Regards Supreeth 8904609867

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.