Formula won't update until I edit the cell?

Hi, I have a spreadsheet to calculate head loss in pipes, and the cell that is giving me trouble is the friction factor, which contains an iterative function, derived from here:

http://excelcalculations.blogspot.ca...tion-with.html

This particular cell will not update whenever a value is changed in a corresponding cell whose values are used in the friction factor calculation.
Automatic recalculation is already enabled, running Microsoft Office Professional Plus 2010.

Is there a fix for this?

Hi -

Are you talking about the spreadsheet you download from the website or is there a different spreadsheet that is causing the problem?

No, I just copied the formula into my own spreadsheet

Can you post your spreadsheet to this forum so we can take a look at it?

Yes, iterative calculations have been enabled, if they were not, the circular reference formula wouldn't have worked.

I've uploaded the file:

Originally Posted by MrShorty

I'm not going to be able to debug this for you. A few strategies I tend to employ to debug this sort of thing:

1) Double check all formulas to make sure they are referring to the correct references. I have trouble viewing files in the newer format, so I could be mistaken, but my viewer showed at least one cell that had a formula in it without the = sign, so it was showing up as text. Perhaps someone here who does not have trouble with the newer file formats will take a look at it and can see any errors like this.

2) Many times, I will revert to a smaller version of the spreadsheet to see if the error is present in all spreadsheets, or just the one spreadsheet. Does the file downloaded from excelcalculations work correctly? If you build a smaller version of the spreadsheet from sratch, does it work correctly? If these don't work, then it may be easier to debug from a smaller, simpler spreadsheet. If these do work correctly, then it might be easier to gradually build them up to the full size spreadsheet rather than trying to fix the existing spreadsheet. It will also give you an opportunity to observe at what point in building the spreadsheet the problem recurs.

3) You might look at using the formula auditing tools http://www.gilsmethod.com/formula-au...ols-excel-2010 to see if you can see anything that should not be in the formulas.

Thanks a lot for your comments. It seems like recalculation is messed up only when editing certain cells, editing others does absolutely nothing, and both these particular cells have the same effect on the circular reference formula. Wierd.

Hi -

I doubt this is your problem, but one concern I have is I'm comparing the formula on the website versus the equation and I'm not positive the equation is correctly written. According to the math on the website, then entire equation is squared. But I think the excel formula is only squaring the denominator rather than the entire expression. As a test, I added parentheses to the left of the 1/... and immediately before th ^2 and the equation produces different results. I think Excel's order of precedence is to perform exponents before performing multiplication/division. I haven't worked with friction factors in a long time so I can't look at the result and tell if it's reasonable. But it does not appear the excel formula provided on the web site matches the mathematics shown on the website.

Good luck.

Hi -

I checked the iteration settings and the spreadsheet you have posted is set to iterate 9,999 times. I set it down to 500 and it seems to be much more responsive to changes. Give that a try.

Hope this helps.