Formulas returning nonsensical results

[Excel_Building_Eng50]

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.

[#2]

Also, yes, I've ensured that calculations are turned on.

[#3]

@Excel_Building_Eng50 , do you mean under "calculations turned on" what they are in automatic mode?

[#4]

@Sergei Baklan , yes calculations are automatic.

[#5]

Can you upload your file please? I'm unable to reproduce.

You might try the Evaluate Formula feature on the Formulas tab.

[#6]

@Zack Barresse , SS attached.  Error I previously mentioned is on sheet 'CODE'.  A similar error is occurring on sheet 'WEBHOLES' in cell F6.

[#7]

@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.

[#8]

That doesn't have anything to do with the problem, although it should be addressed. There is only one Windows API reference, which is in module 'Sub_GetBeamData':

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

It should be like this:
#If VBA7 Then
Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)
#Else
Private Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
#End If

You could wrap it in a function, such as the below code, assuming it's utilizing the above [conditional compilation] code and resides in the same module:

Public Sub API_Sleep(ByVal Milliseconds As Long)
Sleep Milliseconds
End Sub

We can use that conditional compilation because VBA7 can accept ptr safe, which includes 64 bit, and previous to VBA7 was only 32 bit. Full information on how it works can be found at the below link.

https://answers.microsoft.com/en-us/msoffice/forum/all/that-whacky-conditional-compiler-constant/3da...

[#9]

@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!

[#10]

There's several circular references. Since iterative calculations aren't turned on, I'm assuming this isn't desired. I've been tracing formula results which, when manually calculated, are returning a zero value. Some additional oddities is if you try ISNUMBER() on those specific cells you mentioned in your original post, it returns FALSE. Likewise, if you manually calculate those cells, they all return zero values as well. I'm not sure how long it will take me not being familiar with your model. Assuming for the moment that circular references are NOT desired, I'd recommend you pulling on that thread and tracing calculations back to where the circular reference originates, which you'd have the best luck finding as well.

[#11]

@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.

[#12]

@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.