IF formula sometimes returning #VALUE! error unless I hit F2

[ncjefffl]

I'm getting a weirdness using the following formula (which uses labels):

=IF(ParOtc="PAR",SUM(NegNetBenAmt+POSNetBenAmt),Referral Amt)

NegNetBenAmt,PosNetBenAmt,Referral Amt and the cell in which the formula lives are all formatted the same (Accounting, 2 decimals). In some cells (same column) it works and in some I get the #VALUE! error. However, all I have to do is hit F2 and the #VALUE! error goes away and the correct number appears. I can live with the mystery if I must, but it's annoying to have to F2 my way down a column when there are dozens of cells to fix.

I've tried going into Options, Calculations and hitting "Calc Sheet" or "Calc Now(F9)" and nothing happens. Calculation is set to Automatic, Iteration is unchecked and under workbook options all are checked except Precision as displayed and 1904 date system.

Thanks!

 

[ncjefffl]

I meant to ask: failing another solution, is there a way you can "F2" an entire column at once without having to "F2, enter" for each cell?

 

[RoryA]

Assuming you mean labels rather than defined names, I'd suggest you get out of that habit - Excel 2007 onward does not support it. I haven't used them for a very long time, but I seem to recall they weren't always reliable.
PS If you want a quick refresh, select the cells and do a Find/replace replacing = with =

 

[ncjefffl]

I did mean labels, not defined names and thanks for that info; good to know!

The find/replace = with = worked great. I'd still like to know why the problem is happening, but at least I have a much quicker fix now.

Thanks!

 

[mbmast]

So... it's almost 12 years later, and I'm having the exact same problem with the Office 365 version of Excel. And the same workaround works great for me as well. Microsoft continues to polish the turd.