CODEWITHSUNDEEP
excelexcel-formulaexcel-2013
Volvox
Volvox

Reputation: 621

Excel 2013 Forumla Not Working in some Cells

SO I'm trying to get some work done in Excel 2013, and after getting an initial formula to work, I dragged it across several other cells. I did all the standard locking of references and such, even copied the exact formula into the new cell formula window with copy paste. The formula works in the first cell, not in those it's dragged over. It also works in seemingly random cells when pasted around (see screen shot).

Formula is below... this was a brand new sheet, the cells had not been initialized until the formula was pasted. Is this an issue with Excel 2013? Since the formula works in some pastes perfectly, and not in others, I don't think that's the issue... I changed the cell format after the pastes in a couple cells to see if that changed results at all - it does not.

Update: I added in the false value, and the IF is evaluating to false on the zero cells... why it's correctly true in some, and false in others is still a mystery.

=MIN(IF('Metrics'!$DL$2:$DL$1725=$L$2&$O$2,'Metrics'!$DH$2:$DH$1725))

enter image description here

Upvotes: 0

Views: 39658

Answers (4)

PGill
PGill

Reputation: 1

turns out my 2003 excel problem was due to some circular formulas. Had "Circular:" at the very bottom line. When these all fixed, the other cells started to calculate properly.

Upvotes: 0

Will
Will

Reputation: 11

I had a similar if not exactly the same issue. Try hitting F9 to recalc the sheet, or go to File>Options, go to "Formulas" and under "Workbook Calculations" set it to "Automatic".

Solved my problem, which manifested the same way yours did.

Good luck!

Upvotes: 1

Volvox
Volvox

Reputation: 621

The issue is that for the formula to correctly evaluate it should be set as an array (ctrl-shift-enter). However, Excel 2013 seems to randomly recognize the formula as an array formula, and sometimes not. Thus the formula occasionally will work, in a brand new, uninitialized cell, using/displaying the standard formula, and a correct result. Other times it will still display the same formula, but produce incorrect results since it is not being evaluated as an array.

Any of the cells that did not magically convert to an array formula, when changed to array, will update with correct values.

Only manually changed cells reflect the {=MIN(IF(...)...))} indication of an array formula. Cells in which the formula produced correct results still display as a non-array formula.

Upvotes: 2

bonCodigo
bonCodigo

Reputation: 14361

Since you are,using $ sign to lock the cells in the formula, it looks like you are just pointing it to the same cells giving you same results... Click on the formula and check or change to the correct reference cells as you desire...

Upvotes: 0

Related Questions