Comparing two decimal amounts

Question

Comparing two decimal amounts

I have a series of linked spreadsheets relating to my Rotary Club's accounts, and - specifically - our Gift Aid records. One sheet records the amount of Gift Aid claimable per project whilst the other records the individual donations. I need to check that both are showing the same amounts and compare two fields, one in each of the two separate spreadsheets. The cells being compared are both just 2 decimal places yet one is calculating the amount (incorrectly) to 7 places of decimals with the result that the two fields do NOT agree although - on screen - both do so. The amounts are 1,163.14 in both cases but when they are compared the second one is stated to be 1,163.1403125, hence the system telling me they don't agree.

Any ideas?

iMac 27″, macOS 14.2

Posted on Feb 15, 2024 11:17 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

31,351 points

Feb 15, 2024 1:25 PM in response to mikefromnorwich

It appears that the one numbers is from a bunch of additions/subtractions of currency numbers (2 decimal places) and the other had some division operation(s) somewhere along the way that created the extra decimal places.

Formatting a cell to 2 decimal places only affects what you see in the cell. The underlying number in the cell may have many more decimal places. ROUND(your_formula, 2) will round the number in the cell to 2 decimal places.

"your_formula" is the formula you have in the cell right now.

Reply

Answer 2

Feb 15, 2024 1:51 PM in response to Badunit

Thanks: I can see what you mean. The figure in the first of the two tables is the sum of 13 different amounts, each expressed as 25% of the total given for that particular charity whereas the figure with which it is being compared is 25% of the overall total (ie one calcualtion instead of 13).

I've not come across "round" before so I'll do some digging and work out how to use it. Thanks very much for taking the trouble to point me into a direcion I didn't even know existed!

Cheers

Mike Josey

Reply

Comparing two decimal amounts

Similar questions