Incorrect calculation until 'Calculate Sheet' is clicked numerous times

[LoneWolf3574]

Pretty much as the title suggests. I'm using VLOOKUP to generate a table, then creating an average of each row and finally calculating a difference between 2 of the averages. Up until yesterday everything was working fine and now nothing will work correctly unless I click 'Calculate Sheet' at least 2 times (3 done to be certain). Unless someone pays attention (nobody really does) this ends up creating a huge mess with our daily efficiency calculations. I've included some of the formulas I'm using below and the actual workbook below that.

Please Login or Register  to view this content.

I'm at a loss as to what has happened here, so any help or ideas on how to fix this would be greatly appreciated, thanks.

Report for May 2013.xlsm

[FDibbins]

OK 1st thing when I opened your file...
you have a circ ref...this will mess up some of your calcs

I will see if I can find it

[FDibbins]

OK...
In E9 down, you have this...
=IF(E24="","",IF(IFERROR(VLOOKUP($A$8,Thermals!$A$5:$Q$376,4,FALSE),"")=0,AVERAGE(F9:P9),IFERROR(VLOOKUP($A$8,Thermals!$A$5:$Q$376,4,FALSE),"")))
Then in F9, down, you have this...
=IF(F24="","",IF(IFERROR(VLOOKUP($A$9,Thermals!$A$5:$Q$376,4,FALSE),"")=0,AVERAGE(E9,G9:P9),IFERROR(VLOOKUP($A$9,Thermals!$A$5:$Q$376,4,FALSE),"")))
In your 1st formula you are calc'ing an average including F
in your 2nd calc, you are calc'ing an average including E

Also, Im not sure what you are trying to do with all the if(iferror()"s?
I think all you need is...
=IF(F24="","",IFERROR(VLOOKUP($A$9,Thermals!$A$5:$Q$376,4,FALSE),""),AVERAGE(E9,G9:P9))

[LoneWolf3574]

I'm aware of the circular reference, because of the way I set up the formula to generate an average for the row, I felt it was a necessary evil. I know it can be "silenced" by going into the Options, selecting Formulas and setting the Maximum Iterations to "1". Do you think this could be causing the issue on the worksheet?

As far as the IFERROR(), it's part of the original formula for the tables on this sheet. When I found out that I would have to go through 450+ cells and remove nearly 1000 IFERROR(*,"") from my formulas, I felt to much time and effort would be spent doing it, so I left them in place. Or maybe this is the cause?

The formula you give above returns a "To many arguments" error, but if it is rearranged as below, I get a similar result to what I think you are trying to say except a "0" is given instead of just leaving the cell blank if F24 is blank. I was just trying to make the average exclude "0" as it went; with "0" in the table, the average was thrown off. However, changing to this, hasn't fix my problem , but it did clean up the formula some.
=IF(F24=0,AVERAGE(E9,G9:P9),IFERROR(VLOOKUP($A$9,Thermals!$A$5:$Q$376,4,FALSE),""))
or even the following to exclude the "0" from the average in each row.
=IF(F24=0,AVERAGE(AVERAGEIF(E9,">0"),AVERAGEIF(G9:P9,">0")),IFERROR(VLOOKUP($A$9,Thermals!$A$5:$Q$376,4,FALSE),""))