Lookup Function updating inconsistently despite Auto Update of Calc and S/C/A/F9

[Svandyke]

I have a spreadsheet we use to track starting balance, current balance, and possible discounts. The cell with my function should show the right most value, ignoring zeros, but anything in the Final column (including zero) should be returned. This allows us to play with discounts so there is a formula in those cells, and if a final number is entered, then that overrides anything else. Here is my function (resides in column D): =IF(M14="",(LOOKUP(2,1/(G14:L14<>0),G14:L14)),M14)

It all works fine and dandy, some of the time. My issue is that you can enter a value in the discount cell, and the calculated value should show up in D (a reflection of the calculated amount in column I) but sometimes it doesn't. And it's not consistent. I've been playing with adjusting the percent value, and some of the cells in D will update and some won't. The value in Column G seems to be the one most "liked", and the values in column I, not so much. If a value goes in M, it is faithfully returned.

I have calculations set to update automatically, and I've tried Ctrl/Alt/Shift/F9, and I can actually watch the values change in column D if I do it several times. Of course, if I click into the function window and hit enter, it's perfect, but change my discount amount, and it gets wonky again. I can not figure out a pattern.

I would love some insight to the Lookup function, and if there are alternatives to it, or something else that may be contributing to the issue. I have many rows, so manually clicking in each one is not my favorite solution!

Thank you so much you big brained gods and goddesses. This account is just a year old, but this has been a great resource for me for many, many years!

 

[XOR LX]

Hi,

Any circular references anywhere?

Regards

 

[Svandyke]

No circular references.

 

[XOR LX]

And Enable Iterative Calculation is not enabled in File/Options/Formulas?

If not, can you post one or two examples of the data you have, perhaps for one case for which it works as desired and another for which it doesn't?

Regards

 

[Svandyke]

Iterative Calculations is not enabled. The behavior is in any copy of the spreadsheet, which is created from a template. I wish I had a case where updating the discount worked consistently. I can't really demonstrate the behavior in a screenshot, but am happy to send the file. Willing to engage in a contract if necessary!