06-06-2019 07:28 AM
06-06-2019 07:28 AM
I recently had a few formulas start returning nonsensical results and am at a total loss. For example, a formula that takes the max of (0.095, 0.095, 0.2995, 0.2995) is returning 0.7787. When I 'F9' the max formula cell it returns the correct value. I've attached screenshots showing this. Has anybody else experienced this or know how to correct this?
Thanks in advance for your help.
06-06-2019 11:15 AM
06-06-2019 01:22 PM
@Excel_Building_Eng50 , as soon as I try to calculate something in this sheet (e.g. in any empty cell =1+2) it forwards me to VBA. It gives an error
but that doesn't matter, I guess you have no such error. The point is your VBA code is involved into the sheet recalculation.
06-06-2019 01:31 PM
06-06-2019 02:09 PM
06-06-2019 02:22 PM
06-06-2019 02:34 PM
@Zack Barresse , Ah ok thanks, that makes sense. This actually came about because we were trying to remove a circular reference that appeared to serve no purpose. As I'm sure you've noticed this is a very old spreadsheet, and the originator is no longer with us, so we're still trying to get up to speed on the inner-workings.
06-06-2019 02:46 PM
@Excel_Building_Eng50 quite the daunting task! The spreadsheet is fairly complex. Not being the creator will make it significantly more difficult. Although it might take more time, you might want to think about re-building it in portions and replicating as you go, ensuring the calculations are appropriate and efficient. I feel bad suggesting this, because with a model with this level of complexity, I certainly understand just how large of an undertaking that is. Or, probably what I would do, is take some time to become intimate with the current model and excise the circular reference. It would take someone with a lot of patience and understanding of your model but can certainly be done.
by PeterR_TS on May 28, 2020
by Blake T Walsh on June 18, 2018
by Carlos Otero on September 22, 2017