Windows 10 vs Windows XP - Excel Office 365 vs Excel 2010

[ststern45]

Hi everyone,

Wow I am really baffled after upgrading to Microsoft 365 Excel.
Formulas don't calculate in my Windows 10 64 bit machine.
A simple index/match formula gives me the same result in a column. Whereas on my Windows XP Excel 2010 the same formula works to perfection.
Not sure if this is the reason but I have both Excel 2010 and Excel 365 installed on the same Windows 10 computer.
Any ideas on why the formula will not calculate?
The formula example, index($A$1:$A$100, Match($C1,$B$1:$B$100,0)) Cell C1 = ACD

 

[StephenCrump]

What do you mean by gives me the same result in a column?

It sounds like you're describing the new Excel 365 dynamic functions, and the spilling of a formula into adjacent cells? But the Index/Match formula you've posted won't spill.

When you say, the formula will not calculate, what exactly do you mean? Does it give you a result you think is incorrect? Show as a formula rather than a calculated result? Or what?

 

[Peter_SSs]

One possibility is that your 365 is set to Manual calculation of formulas instead of Automatic. Have a look in File -> Options -> Formulas -> Calculation options

 

[ststern45]

Thanks for the replies.
The formula worked when I did the following and I'm not sure why.
I did have the option set to automatic no manual. That is, I never changed this option.
The cells in the column originally had alpha characters and I wanted to change these cells with the index/match formula. I deleted all the alpha characters in the cell range and also removed any formatting in all the cells. The only formatting was was a "border" in all the cells so I removed the borders. So this gave me a "clean" if you will cells in the column cell range. I re-entered the index/match formula and it calculated correctly. I'm not sure if having a cell formatted with a border throws off the calculations or entering a formula in an existing cell that contained formatting.

 

[RoryA]

You still didn't explain exactly what you were seeing. Were you seeing the same formula result in every cell, or were you seeing the actual formula in every cell? The latter would suggest that the cells were formatted as Text initially.