Locate cells in large range with last decimal place not equal to zero

[Ironman]

Hi

Range B2:Q367 displays values with one decimal place, as there should be no values containing 2 decimal places other than a 0. However, when I summed the range, it returned 10,715.61 and not 10,715.60 as I would expect.

As there are too many cells for me to look for myself, I would be grateful for a formula or VBA that will return the cell or cells in the above range that have a 2 decimal value not equal to 0.

Many thanks!

 

[offthelip]

There is quite a possibility that none ofthe values have a second decimal place but the sum still has a 2 decimal places, You need to read and understand this article: about floating point precision:
Understanding Floating Point Precision, aka “Why does Excel Give Me Seemingly Wrong Answers?” - Microsoft 365 Blog.

 

[Ironman]

Thanks, but I have identified 2 such values in the range and would therefore prefer to work on that premise.

 

[Fluff]

How about using conditional formatting to highlight the cells using

=LEN(MOD(B2,1)*10)>1

 

[Ironman]

Great idea Fluff, resolved - many thanks!

 

[Toadstool]

Thanks, but I have identified 2 such values in the range and would therefore prefer to work on that premise.

That's a shame because if there's one and one only then this ugly formula would work.

=ADDRESS(SUMPRODUCT(((ROUND($B$2:$Q$367,1)<>($B$2:$Q$367)))*ROW($B$2:$Q$367))-ROW($B$2:$Q$367)+1+ROW($A$1),SUMPRODUCT(((ROUND($B$2:$Q$367,1)<>($B$2:$Q$367)))*COLUMN($B$2:$Q$367))-COLUMN($B$2:$Q$367)+1+COLUMN($A$1))

 

[Ironman]

Wow, that's quite some formula - thanks a lot for the thought you've clearly put into that Toadstool, I'm sorry I was unable to use it

 

[Fluff]

Glad we could help & thanks for the feedback.