MOD(x,1) returns 1 (*Incorrect*)

I found an oddity in using MOD just recently where it's returning 1 when it should return 0. I'll post the cell's formula for sake, but all the appropriate cells are accurate.

This is in cell Z29:

Please Login or Register  to view this content.

This is a simple SUM with an indirect lookup that is basically 2 cells above Z29, so B27:Z27. The result is "72250.00", without quotes (real number, not a string). The problem is that MOD(72250.00,1) returns 0, but MOD(Z29,1) returns 1. There is some phantom decimal occurring where there shouldn't be, and MOD is apparently rounding it "UP" of all things. No matter how many decimal places I show, the decimal portion of Z29 is always zero's (72250.0000000...).

Is there a way to fix this w/o forcefully using ROUND or INT prior to using MOD? And yes, this is used in other cells where using ROUND or INT would interfere with the outcome, and the data is liable to change, so I have reason for not wanting to Band-aid a broken function.

P.S. I've tested this in OpenOffice's spreadsheet and it works fine there, but I prefer to use Excel.

Can you post the workbook?

Chances are the sum returns 1 LSB less than 77250.

Originally Posted by shg

Can you post the workbook?

Chances are the sum returns 1 LSB less than 77250.

Here's the workbook as is: Book1.xlsx

It shouldn't be 1 LSB less then 72250; the cell reports 72250.00, and even if it was just 1 LSB less, it should yield a decimal remainder instead of rounding autonomously. As a better explanation of what I was intending to do, I was converting a total number from minutes to a readable format manually, and the result should be "1204:10:00" for 1204 hours, 10 minutes. Instead it's returning "1204:9:60". As I've stated, it works properly after I tested it in OpenOffice to verify, just not in Excel.

Originally Posted by Cutter

An alternative would be to use:

=Z29-INT(Z29)

May I ask why you have all those INDIRECT() functions?

Using INT would eliminate any decimals that I may want to preserve. Your alternative also removes the value I want, leaving whatever LSB might be leftover. The cells are populated based on the high/low values set at the top, so Z29 isn't always going to be a round number, it just happens to be in this case. This also doesn't fix other cells with the same issue, like F24.

As for the INDIRECT functions, I originally copied the formula from another spreadsheet I did that where the INDIRECT would need to alternate positions, I just hadn't cleaned it up when I simplified my spreadsheet. It made it easy to populate a large number of cells for a graph with the INDIRECTS, instead of finding another way to pick the appropriate cell. It was necessary at the time for the desired result.

The value in Z29 is 40F1A39FFFFFFFFF, which is indeed one LSB below the whole number, and =MATCH(72250, Z29, 0) returns #N/A