Cell Returns Zero On Formula Entry When Values Exist In Dependent Cells

[SimonHughes]

I have a workbook with multiple worksheets and there are formulas throughout. I am now trying to create another formula which is a simple divide of cell C3 by cell E3 (both are formula driven) and I get a 0 (zero) return. I have tried inserting a new worksheet, no help. I have tried inserting a new column between existing formula driven cells, no help. All existing formulas recalculate successfully on new data entry.

Most bizarre, can anyone help?

 

[mole999]

you returns, would they be between -0.6 and 0.4 as they might round to zero. the cell you are expecting to see with a result is it set as text ?

 

[SimonHughes]

I have checked that and it's not the problem. I am really baffled by this as I am no stranger to Excel. I tried saving, closing and opening, entering the formula in different parts of the worksheet, opening new worksheets but nothing works.

HOWEVER - If I over-type one of the existing formulas with the result, then the calculation works. So the problem seems to be only where I am working with two cells, each of which contains a formula

 

[mole999]

rather than relying on two cells to evaluate, csn you combine the formula in the cell where you need the final result

 

[SimonHughes]

rather than relying on two cells to evaluate, csn you combine the formula in the cell where you need the final result

Nope, that's not working but even if it did, the original problem would still be here.

 

[mole999]

so what are the errant formulas ? and inputs

 

[SimonHughes]

so what are the errant formulas ? and inputs

Formula in cell Q4 =IFERROR((S4/R4)+Q3,"") - The result of this formula is 400.14
Formula in cell U4 =IFERROR(HLOOKUP(A4,'Unit Divis'!$D$2:$W$14,13,0),"") - The result of this formula is 70.08
Formula in cell W4 =U4/Q4 - The result of this formula is 0.00 but should be 0.18

If I overtype 400.14 in cell Q4, then the result in W4 changes to 0.18, It doesn't like the dependent formulas for some reason

 

[mole999]

first thing, drop the IFERROR whilst testing. i assume calculation is set to automatic so you will see changes to the cell live