MOD function "feature"

[quisizyx]

Greets- Ran into bogosity where MOD (N,12) gives a result of 12 when N is evenly divided by 12 so should give a result of 0! Any1 run into this B4? Samsung Tab Pro 12.3 inch tablet w/Android running office suite app Polaris.

 

[shg]

Chances are N is a skosh < 12.

 

[Joe4]

Welcome to the Board!

It may be that you are running into the Floating Arithmetic Error.
See:
https://support.microsoft.com/en-us...ithmetic-may-give-inaccurate-results-in-excel
Floating Point Errors in Excel - EASY Excel Tutorial

If that is the case, you can get around it like this:
=MOD(ROUND(N,0),12)

 

[quisizyx]

Thanx. Will try that.

 

[quisizyx]

Actually, N is a lot bigger than 12. 33,012. Which 12 divides into 2,751 times. Evenly. As I posted. No remainder.

 

[Joe4]

What do you get if you enter these formulas directly in Excel?
=MOD(33012,12)
=MOD(ROUND(33012,0),12)

If those work, then it is something about N.
Where is N coming from? Is it a calculated field?
If so, what is the exact formula?

 

[Rick Rothstein]

What do you get if you enter these formulas directly in Excel?
=MOD(33012,12)
=MOD(ROUND(33012,0),12)

If those work, then it is something about N.
Where is N coming from? Is it a calculated field?
If so, what is the exact formula?

Hmm, 33012 is a little bit larger than what an Integer variable can hold... I wonder if the OP maybe declared the N variable as an Integer... if so, the OP should change the declaration to Long instead.

 

[quisizyx]

Greets- INT function was flaky as well. 33,012 inches was turning into 2,750 feet [INT(33012/12)]. Then the remainder was 12 inches [MOD(33012,12)]. Fixed Feet column w/ =IF(MOD(ROUND(33012,0),12)=0,INT(33012/12)+1,INT(33012/12)). Then I fixed the inches column w/your suggestion =MOD(ROUND(33012,1),12). That made it 2,751 feet which is correct & 0.0 inches. I find it weird that this android office suite app has the same issue as MS Excel. Makes me think some1 duplicated some code. Thanx 4 the assist.

 

[Joe4]

I wonder if the OP maybe declared the N variable as an Integer.

It looks like an Excel formula, not VBA, so I don't think there is probably any variable declaration happening!

 

[quisizyx]

What do you get if you enter these formulas directly in Excel?
=MOD(33012,12)
=MOD(ROUND(33012,0),12)

If those work, then it is something about N.
Where is N coming from? Is it a calculated field?
If so, what is the exact formula?

Greets-
The weirdness gets weirder. INT & MOD functions seem to work fine in certain areas of the spreadsheet while it doesn't in others.
The spreadsheet uses input for the Sun's diameter to generate the scale distances for the 8 popularly known orbital objects of the Solar System. Most actual measurements are in kilometers. The desired Sun diameter value entered is used to find the ratio (actual:scale model) then convert from metric to feet & inches. A random check of rows for various orbital objects w/a calculator app appear to verify the accuracy of the INT & MOD functions w/one exception, Saturn. It is the only value that 12 divides into evenly. This is the only instance that the floating point "feature" appears to kick in.11.99798836

There are 2 fixes that I implemented for feet and inches. For feet I used the test =IF(OR(MOD(n,12)=12,MOD(n,12)>11),INT(n/12)+1,INT(n/12)) which tests for the remainder ether equal to the divisor (12) or one less plus a bit of the divisor (11.99998) and if found adds 1 to the number [INT(n,12)+1] to get the correct answer or leaves it alone if the floating point "feature" isn't found. The problem with this test is if 11.nnnnn is the actual correct remainder. The bogus MOD answer is 11.99798836 so if I test for >11.99 this will narrow down the possibility of hitting an actual correct answer. The tendency here is to use the test only in the cells that divide evenly. This requires scanning down the column looking for remaining inches of 12 which is a foot which means the Feet column should have 1 added and the Inches column 0. This is how I found the error in the first place. This might be problematic if the cells aren't dealing with feet and inches. Scanning through the spreadsheet to find a value in the remainder column equal to the divisor so you can put the test in those cells is a pain and, if a different initial value is input, is not transparent or nonimpactfull to the correct answer as a different initial input value may mean that those cells that divided evenly may not with the new value when the spreadsheet recalculates.
In an attempt to find the trigger for the "feature" I created some test cells to the right of the cells with the error with various tests and different number of places to the right of the decimal. None seemed to trigger the "feature". All instances of INT and MOD seemed to work properly. Thinking that some maximum number triggered the "feature" I created some test cells below the spreadsheet that started at 12 inches and doubled the number of inches for each next cell below. Since all of these values divide evenly I expected some maximum value to trigger the "feature". At 33024 which divides evenly 2752 times I stopped as the error occurred in the spreadsheet at 33012 which should have shown 2751 but showed 2750. Just for grins I put 33012 in between the last doubling (33024) and the previous doubling (16512). They all worked correctly! Even the one that continues be wrong within the spreadsheet!
Totally confused and frustrated I thought that perhaps the position of the cell with the error amongst the other cells that DON'T divide evenly perhaps triggers the "feature". I created a sheet with the column that held the scale orbital diameter in inches then the columns that derived the feet and inches. Since all but one did not divide evenly I inserted, about every nine rows, one from the the doubling test that would divide evenly. Every one worked correctly! Even the one that previously gave the bogus answer!
I'm at an impasse. Further testing would require more complex and/or complicated configurations. Also more time than I'm willing to commit. Perhaps it takes more than 9 rows of cells of non-evenly divided values to trigger the "feature". Perhaps it takes certain cells in columns to the left of the bogus result cells to trigger the "feature". Perhaps it's a combination of certain number of cells above that don't divide evenly with certain cells in columns to the left including a certain maximum number. I find it very odd that only those 2 cells in the whole Scale Solar System spreadsheet appear to have the "floating point" issue.
Can anyone present me with a test that will correctly figure feet? There is a Catch 22 here as I seem to keep running into the floating point issue when testing for the floating point issue.
Think I will go stare at the MS website mentioned to C if I can get some clues to a fix.

The light at the end of the tunnel is a brightly lit room with a blank wall that has a small 6 inch door. There are two bright spot lights. One shinning on a giant 30 foot high table with a large piece of cake next to a sign reading "Eat Me". The other light shinning on a 3 inch high table with a tiny bottle next to a sign reading "Drink Me".