Cell data comparison not working

[abdulhaque]

Hi all,

I'm comparing two cells data (G50 and G66) to see if they're equal, each is calculated via a formula, and the results are not correct. The comparison formula is =IF(G50<>G66,1,0). Both cells data evaluates to £12499.00, so therefore I'm expecting the result of the comparison formula to be 0, instead I'm getting 1.

This comparison works elsewhere. The only difference between where it works and not is in how G50 and it's referenced cells are calculated. Any idea where it fails?

Where it doesn't work
G50 =SUM(G48:G49) = £12499.00
G48 =G46+G47
G49 =(G46+G47)*20%
G46 =G44*G45
G47 =Numerical entry, currency
G44 =SUM(G2:G43)
G45 =Numerical entry, currency
G2:G43 =Numerical entry, integers

G66 =SUM(G51:G65) = £12499.00
G51:G65 =IF(LEFT(A51,1) = "S", IF(H51="", " ", SUMIF(H$2:H$43,H51,G$2:G$43)*$G$45)*(1+F51), "")

Where it does work
G44 =SUM(G2:G43) = £5760.00
G2:G43 =Numerical entry, currency

G62 =SUM(G47:G61) = £5760.00
G47:G61 =IF(H47="", " ", SUMIF(H$2:H$43,H47,G$2:G$43))

=IF(G44<>G62,1,0) evaluates correctly to 0

 

[Oscar Cronquist]

I'm comparing two cells data (G50 and G66) to see if they're equal, each is calculated via a formula, and the results are not correct. The comparison formula is =IF(G50<>G66,1,0). Both cells data evaluates to £12499.00, so therefore I'm expecting the result of the comparison formula to be 0, instead I'm getting 1.

Are all decimals visible in G50 and G66?

 

[jmacleary]

Hi there

I suspect the problem may lie with G49 - you are calculating 20%, and if that doesnt come out to a round number of pence there will be some rounding. Try setting the format of cells G50, G66 and G49 to General to see if they are completely the same value.

 

[abdulhaque]

Are all decimals visible in G50 and G66?

Yes

 

[abdulhaque]

I suspect the problem may lie with G49 - you are calculating 20%, and if that doesnt come out to a round number of pence there will be some rounding. Try setting the format of cells G50, G66 and G49 to General to see if they are completely the same value.

Thanks, upon changing to General format, there were differences in the decimals. I've rearranged my totals and checks to ignore the percentage calculations.