On 5/24/2021 6:07 AM, dpb wrote:
On 5/23/2021 12:05 PM, David Farber wrote:
On 5/23/2021 9:53 AM, Claus Busch wrote:
Hi David,

Am Sun, 23 May 2021 09:19:41 -0700 schrieb David Farber:

That works fine. Thank you. Do you have any idea why Excel glitches on
my formula?

for me your formula works fine.


Regards
Claus B.

Does it work for you (meaning Excel doesn't modify the formula in
column "I") even if you enter an "X" in one cell, and then change your
answer by cutting and pasting "X" into another cell? Maybe a later
version of Excel has fixed the problem?

That's by design; it's typically the desired result to have the formula
adapt to the data; your case is an exception but there's no way in Excel
to change the behavior.

I've learned the hard way, too, in some cases in which I also did not
want the behavior.Â* It also follows with sort() in which reordering the
data can cause formulas to change their targets as well, sometimes with
disastrous results.

--


As I pointed out earlier, OpenOffice's Calc does not exhibit this
behavior. I'm still curious as to when does this data mismatch occur
which triggers the #REF! For example if a cell is blank, there is no
error. If I put anything in the cell, whether it be a number or text,
there is no error. Now if I use the "Cut" operation, that would leave
the cell blank again and that certainly was OK back when the formula was
created. So why is it a problem now?

Thanks for your reply.

--
David Farber
Los Osos, CA