Excel returning wrong logic

[mike4777]

I have a simple formula X>X where the values of X are decimal numbers returned from an API from CoinMarketCap (cryptocurrency prices).

Excel is telling me that .0001 is greater than .0002

I tried using the max formula too and that worked for some cells and not for others (no pattern to this).

Never seen this before. Any ideas?

 

[mole999]

what are those values to 15 decimal places

what is your real formula X>X is always going to be false

if they were negative values, what you have is true

 

[mike4777]

there are a lot of cells of decimals but one example:

.00003953>.00003500 is being returned as false.

I should also mention, I have another column of numbers from this api table (returned to another sheet with index/match functions). These numbers are dollar amounts that will not allow me to change the format from .15 to $.15 (currency format) despite the fact that it says it is on currency format.

The X>X IS the formula expressed abstractly.

 

[mike4777]

I just confirmed that the formula returns the right logic when I make another column next the the API column values and type all the same values in the new column. Is there a setting that will allow the index/match values from the API table to function correctly?

 

[mole999]

are any of the api values coming in as text, test with =ISNUMBER(cell ref) should be TRUE. visually if there are no cell formatting, data to the right in a column is numeric, to the left is text

 

[mike4777]

it is false. Is there a way to change the settings of the api?

 

[mole999]

there are a number of things
I think financial rounding, dosen't always work as expected
you might try double minus --X>--X which forces text to numeric
TRUNC could shorten what you see as values (not ideal in finance)
Index/Match has 0 and 1 for exact and nearest