formula wont calculate until I manually hit enter in the cell with the formula... (?)

[kbishop94]

the cell in question is formatted as 'general' (also had it formatted as a 'number'... but nothing changed with it formatted that way, or any other different way for that matter...)



when formula is calculating CORRECTLY, it should populate the cell in the column that corresponds with the source cell (J2) with a "1" (otherwise the cell is supposed to remain blank.)
(in the example below, the number in the source cell, '143', should prompt a "1" appearing in column U, which contains the formula: =IF(AND($J2>=121,$J2<= 150), "1", "")



but that formula ONLY works if I manually double click in the source cell (in column J) and then hit 'enter':
(below it shows where the formula in AX7, which is: =IF($J7>7300, "1", "") is incorrectly showing a "1" (as do all of them in that column )



but after hitting the enter key in the cell with the number 94 (J7), it correctly populates cell T7 (which contains the formula =IF(AND($J7>=91,$J7<= 120), "1", "") ) and also correctly leaves blank AX7 (which it should be)




so far I've tried:

saving, closing and reopening the workbook, turning off and then back on the enable calculations.... and (after finding a possible solution after googling this), manually doing a search and then find and replacing "=" with "=" (equal sign with equal sign) (which surprisingly worked for a number of people who encounter this problem, but not with me! lol)

any ideas on what is doing this? Thanks

 

[Anthony47]

The first obvious question is:
-is the calculation mode set to Automatic? Check in Menu /File /Options /Formulas

 

[kbishop94]

check. (I mean yes, it was set to automatic)



(thank you for the reply and suggestion, btw. )

 

[Anthony47]

So the second question: after you Edit & Enter the formula, does it recalculates (if the referenced cells change) or they still keep needing to Edit & Enter for any next calculation?

 

[Anthony47]

The "Replace = with = " trick is intended to set all the formulas that have been inputted as text; but in this case you should see in the cells the text of the formula, not a wrong result.

My current suggestion is shut down the pc and reboot

 

[Peter_SSs]

My guess is that the values in column J are actually Text, not Numerical. Changing the formatting of the column to General (or Number) does not by itself change the nature of the underlying value in the cell.
In your images, column J appears to be centred. If you select column J and remove the centre setting (by clicking the 'Center' option again), I suspect the values in the column will move to be left-aligned, indicating Text values.

Try this.

1. Select column J and set formatting to 'General'
2. With column J still selected, Data ribbon tab -> Text to Columns -> Delimited -> Next -> Remove any 'Delimiters' options selected -> Next -> Ensure 'Column data format' is 'General' -> Finish

ONLY works if I manually double click in the source cell (in column J) and then hit 'enter':

This is another way to convert the Text value in col J to a Number, but it only does one cell at a time. The above suggestion does the whole column at once.

 

[kbishop94]

My guess is that the values in column J are actually Text, not Numerical. Changing the formatting of the column to General (or Number) does not by itself change the nature of the underlying value in the cell.
In your images, column J appears to be centred. If you select column J and remove the centre setting (by clicking the 'Center' option again), I suspect the values in the column will move to be left-aligned, indicating Text values.

Try this.

1. Select column J and set formatting to 'General'
2. With column J still selected, Data ribbon tab -> Text to Columns -> Delimited -> Next -> Remove any 'Delimiters' options selected -> Next -> Ensure 'Column data format' is 'General' -> Finish

This is another way to convert the Text value in col J to a Number, but it only does one cell at a time. The above suggestion does the whole column at once.

confirmed that after hitting the center-align button once again the cells DID all become left-aligned... good call...

Also, converting all the cells at once by way of using the text-to-columns and removing all delimiters worked perfectly. bravo, sir!

 

[Peter_SSs]

You're welcome. Glad that sorted it.