Trying to ignore a formula in a cell to then calculate

English
#1

I have this

=IF(ISBLANK(M14) OR COUNTIF(M14; "*<FORMULA>*"), "", (M14-$B$14)/$B$14)

which returns Err:508 as there is a formula in M14 but otherwise empty?
B14 is populated with a number

So I need Calc to check the M14 cell for being Blank or containing a formula then if so return a blank empty result otherwise complete the calculation?
Can anyone see why the syntax of my formula might be failing please?
Thank you

Also tried this with same err 508
=IF(ISBLANK(M14) or ISFORMULA(M14), "", (M14-$B$14)/$B$14)

#15

Try this formula…

.=IF($M$14="","",($M$14-$B$14)/$B$14)

#16

Thank you but I have tried this again and it still returns -1 instead of blank. This is appearing because there is an unfilled formula in the referenced cells and if I remove the formula then the return is the desired blank.
Why is the presence of the unfilled formula causing the -1 error is the question and then what to do about it?

#17

Post the formulas, or better post the spreadsheet…

#18

We have difficulties to understand what are the contents of the cells. When you select a single cell, you see:

  1. The content of the cell. This can be a formula, starting with =, a number, a text, etc. You see it at the top of the screen. In the example below, =TEXT(A20,"DDDD")
  2. The visualization of the cell. This is how the cell appears to your eyes. It is surrounded by a blue frame. ==Monday==
  3. The exact content of the cell. This is how Calc has evaluated the content of the cell. It may be different than 2 because a special formatting is applied to the cell. Monday
    image
    image763Ă—668 67.5 KB

And I have prepared a simple example, please modify it as you deem necessary and repost it with your comment.
IgnoreCell.ods (9.9 KB)

#19

Steph
I have found the Answer as this works
=IF(OR(ISBLANK(M14), ISBLANK($B$14), ISERROR((M14-$B$14)/$B$14)), “”, (M14-$B$14)/$B$14)

and the reason that these errors have been happening has got something to do with the fact that these referred cells have an unfulfilled formula residing in them.
I have now edited literally hundreds of formulas across the 3 sheets using this or a similar approach where needed such as =IF(AND(NOT(ISBLANK(F107)), NOT(ISBLANK(D116)), NOT(ISBLANK(E116))), F107+D116+E116, “”)
They are all Formulas that first check the cells some of which have their own formulas in either filled or unfulfilled before proceeding with the calculation or not.
So I now have the 3 sheets all prefilled with formulas in many cells just waiting for the next data input in which case all the calculations will occur instantaneously and there are NIL error warnings showing anywhere. This is the advice I received from my friendly ChatGPT This formula checks for the following conditions before performing the calculation:

  1. If M14 is blank.
  2. If $B$14 is blank.
  3. If there is an error in the division (M14-$B$14)/$B$14.

If any of these conditions are true, it returns an empty cell; otherwise, it performs the calculation (M14-$B$14)/$B$14. Steph I really want to thank you for your considerable effort that you put in whilst trying to assist me with this annoying problem. It is very much appreciated. And to the other members who contributed I thank you also. I feel I should mark this as the Answer, Is that the right thing to do?

#20

Thank you for your nice words!!!

Yes, mark your last post as the answer.

1 Like
#2

The OR function works slightly differently:
=IF(OR(ISBLANK(M14),COUNTIF(M14,"")),"",(M14-$B$14)/$B$14)
You could use as well the ISFORMULA function to detect the presence of a formula in a cell, which is maybe more robust than COUNTIF:
=IF(OR(ISBLANK(M14),ISFORMULA(M14)),"",(M14-$B$14)/$B$14)

#3

Steph Thank you very much. So close as the error is now gone but I end up with a
-1 as a result.
M14 only has an unfulfilled formula in it and B14 has a number so it is a bit confusing ?
Any further great ideas?

#4

Well, if (M14-$B$14)/$B$14 returns -1, it means that M14 is evaluated to 0. Beyond that, not being able to see what M14 contains, I cannot be more precise.

#5

Yes M14 has no numbers in it only an unfulfilled formula which will eventually generate a number to populate in M14?

#6

So, what would you like to return when M14 evaluates to 0?

#7

Nothing just blank, empty until such time as the bespoke formula is triggered by the input of a number in another spreadsheet which will then populate a number into M14.
Hopefully I have explained well enough.

#8

You should use the ISFORMULA formula then

#9

I think what you just need is:
=IF(M14="","",(M14-$B$14)/$B$14)
The function ISBLANK() tests the content of the cell, not its eventual returned value. M14="" tests the value of the cell after the formula is applied (what you actually see in the cell).
When M14 shows a value:

image
image882Ă—342 10.5 KB

When M14 shows a blank cell

image
image891Ă—316 9.58 KB

#10

Hi Steph
Well that simple approach worked and M14 is now blank BUT when I drag the formula to the adjacent columns ie N, O, P etc this produces the -1 to appear in N14, O14 and P14 cells? This is very strange as I don’t see how it can now work properly in M14 but not the adjacent cells which all have the adjusted formulas copied into their row 14 columns?
Could this be a bug maybe?

By way of further explanation to assist with the thought processes I have created a 3 sheet spreadsheet. When I enter monthly data into sheet 1 then the required formulas are set up in sheets 2 and 3 and ready to calculate when that data is entered into sheet 1. There are a number of various calculations that are set to basically self propagate with the data entry BUT up until that point I need these various cells which contain various formulas in them to visually remain blank despite the fact that they have unfulfilled formulas waiting inside them.
The various interreacting formulas work as required in the earlier rows that have the data already acquired so the design is correct. It is just these annoying little errors and -1 figures that are clogging up sheets 2 and 3?
Hope that helps.

#11

Sorry, I am lost. Please upload an example here with the three sheets.

#12

image
image906Ă—732 37.1 KB

image
image913Ă—729 38 KB

The N version introduces this -1 yet N14 is blank save the formula

It is very complicated for me to create a truncated and working version of the 3 spreadsheets and I don’t want to post the complete 3 sheets here. I realise that it makes it harder so I understand your desire for them. When I first posted I thought this was going to be a syntax mistake on my behalf but I now don’t think so??

If I remove the formula from the N 14 cell then the result returned is a blank empty cell as desired but when I repost the formula into N14 the -1 reappears???

#13

OK then tell us what is the content of M14 when you edit the cell (as you said, it is a formula) and what is the content of M14 when you select another cell (what you see, the result of the formula).
Same for N14 please.
Oh, do you mean that M14 contains this IF formula that contains a reference to itself?

#14

Hi Steph
I am away from my laptop until tomorrow but it might be best explained by my earlier attempt quoted again below.

Well that simple approach worked and M14 is now blank BUT when I drag the formula to the adjacent columns ie N, O, P etc this produces the -1 to appear in N14, O14 and P14 cells? This is very strange as I don’t see how it can now work properly in M14 returning blank but not the adjacent cells which all have the adjusted formulas copied into their row 16 columns?
Just for clarity I am posting your simpler formula into N16 M16 O16 P16 etc
All the refered row 14 cells have the identical but adjusted formulas in them yet N16 is the only one to return the desired blank empty cell whilst
M16 O16 P16 all return the -1
But if I remove the formula from say M14 O14 or P14 then they all return the desired blank empty cell(s) except unlike N14 these other 3 do not have the required formula inserted as is in N14.
It is a mystery to me.
But anyhow
Happy New Year to you and a big thank you for all the

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.