Jun 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.
Jun 06 2019 07:32 AM
Also, yes, I've ensured that calculations are turned on.
Jun 06 2019 08:54 AM
Jun 06 2019 10:07 AM
@Sergei Baklan , yes calculations are automatic.
Jun 06 2019 11:15 AM
Jun 06 2019 12:32 PM
@Zack Barresse , SS attached. Error I previously mentioned is on sheet 'CODE'. A similar error is occurring on sheet 'WEBHOLES' in cell F6.
Jun 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.
Jun 06 2019 01:31 PM
Jun 06 2019 02:09 PM
@Zack Barresse @Sergei Baklan , Thank you for point that out. I've since removed the 'Sleep' reference as we were not actually using it for anything.
As expected, the cell formula problems persist. Any further thoughts or findings on what might be the issue here?
Thanks again!
Jun 06 2019 02:22 PM
Jun 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.
Jun 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.