Ifs statement help / results not as expected..

[KJ318]

Hello Community,

I would be grateful for any help with the following problem please:

I have two sheets as follows:

1. Look Up date with values (£) to look up.
2. Main sheet to bring back the values (£) and compare the values (£) against existing values (£)

I've used xlookup to look up the vales and IFS to check the values, for the most part the IFS returns the correct result but for one of the results, the return value is incorrect.

I've inserted a picture of where the result is incorrect and highlighted the row, I cannot figure out what's gone wrong...all values are formatted as Currency, I've check the data with the isnumber() and it returns a true on the look up data and main sheet...is the logic in the ifs incorrect, if so, why is only one particular row showing an incorrect result?

Any help / info would be most appreciated.

Kind regards,
KJ.

 

[KJ318]

 

[joeu2004]

My guess: If you temporarily format D11 and E11 as Number with 13 decimal places (15 significant digits, the most that Excel formats), you will see that D11 is indeed larger than E11.

If one or both columns are calculated, you should explicitly round to 2 decimal places, either in the cells themselves or at least in the IFS expression (column F).

FYI, you can replace the last test (D11>E11) with simply TRUE, since you already established that D11=E11 and D11<E11 are false.

-----

Another possibility: D11 is text, and E11 is numeric. Looks can be deceiving, and the format of the cell does not matter. Use ISTEXT(D11) to determine.

(In Excel, all text is consider greater than any numeric value.)

 

[KJ318]

Thank you, Joe, you were on the money, D11 is larger than E11:

Thank you for the note about rounding and the final test in the ifs function; most helpful.

Much appreciated.

Kind regards