COUNTIF - Formula Not Calculating Correctly...

[wakerider017]

I have no idea why the below formula is not working. Numbers in column A are formatted correctly as numbers.

This formula is in cell B2 and copied down to cell B6: =COUNTIF(A$2:A$6,"<"&A2)
An incorrect result is given in cell B5.

I manually typed the results I would expect in column C.

Any ideas??


[TABLE="width: 399"]
<tbody>[TR]
[TD="align: center"]
[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Number[/TD]
[TD="align: center"]Incorrect Formula[/TD]
[TD="align: center"]Correct Result[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]-25.3576411748195[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]-8.47000433795198[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]-3.61982188879533[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]-9.68909643012068[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]9.50380974252980[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[wakerider017]

Looks like this can't be reproduced by using the numbers I pasted above..

Seems like there is an issue with the number -9.68909643012068 as it is currently stored in my Excel file. I copied this number from another equation I performed.
Very odd.. If I take =VALUE(A5) in a cell to the side, copy and paste values over cell A5 to ensure it is indeed a number... Nothing changes, still wrong... If I click in the formula bar while A5 is selected and then click the check mark, the issue is magically resolved.

What is wrong with the number??

Here is my example file where you can see it in person:
http://amslat.com/test/example.xlsx

 

[Special-K99]

Thinking...

 

[Joe4]

I am wondering if you might have something like this going on: https://www.microsoft.com/en-us/mic...y-does-excel-give-me-seemingly-wrong-answers/

What happens if you make the "Precision as Displayed" update, as shown in that link? Does that resolve the issue?

 

[Special-K99]

Works fine for me
Excel 2013

 

[Rick Rothstein]

I have no idea why the below formula is not working. Numbers in column A are formatted correctly as numbers.

This formula is in cell B2 and copied down to cell B6: =COUNTIF(A$2:A$6,"<"&A2)
An incorrect result is given in cell B5.

I manually typed the results I would expect in column C.

Any ideas??


[TABLE="width: 399"]
<tbody>[TR]
[TD="align: center"]
[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Number[/TD]
[TD="align: center"]Incorrect Formula[/TD]
[TD="align: center"]Correct Result[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]-25.3576411748195[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]-8.47000433795198[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]-3.61982188879533[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]-9.68909643012068[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]9.50380974252980[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]4[/TD]
[/TR]
</tbody>[/TABLE]

I cannot reproduce your problem... when I try your formulas with those numbers in cells formatted as Numbers with 14 decimal places, I get the same results as in your Correct Result column.

 

[wakerider017]

Works fine for me
Excel 2013

I am also running 2013. Did you copy the data in from my first post or did you download the file in the link I provided. If you just copied my data, then it also works for me if I do it like that. Something is not right with the way that number is stored in the original Excel file.

 

[wakerider017]

I cannot reproduce your problem... when I try your formulas with those numbers in cells formatted as Numbers with 14 decimal places, I get the same results as in your Correct Result column.

Hey Rick, same response as what I just posted in reply to K99.

Did you copy the data in from my first post or did you download the file in the link I provided. If you just copied my data, then it also works for me if I do it like that. Something is not right with the way that number is stored in the original Excel file.

 

[Joe4]

wakerider017,

Did you read the link I sent you, and try what I suggested?

 

[wakerider017]

I am wondering if you might have something like this going on: https://www.microsoft.com/en-us/mic...y-does-excel-give-me-seemingly-wrong-answers/

What happens if you make the "Precision as Displayed" update, as shown in that link? Does that resolve the issue?

This does resolve the issue. I am also able to use the ROUND function to successfully resolve the issue.

Sorry, but I do not understand why is this occurring? The numbers are not relatively well spread apart, so I'd imagine there must be a technical reason?