# Modulo differences (LibreOffice v PHP)

I am trying to reproduce some spreadsheet formulae in a PHP script.

``````=MOD((D25-D7),20)+1
``````

which I have written in PHP as:

``````(\$D25 - \$D7) % 20 + 1;
``````

With D25 = 6 and D7 = 12 the spreadsheet gives an answer of 15 and the PHP script gives an answer of -5.

I’m puzzled as to how the spreadsheet (I’m using LibreOffice) gets an answer of 15.

Edit: Hopefully Excel would give the same result, but I can’t check.

Excel matches Libre 1 Like

You’ve run into a debate as to what mod (%) really is, and whether negative numbers are allowed. Excel defined mod to be: MOD(n, d) = n - d × INT(n/d) and with that int() in there, the question becomes how to round a negative integer. php chose to do it purely on the math of calculating the remainder, which is different.

I just wrote this, so do some testing, but it may work:

``````function mod(\$n,\$d)
{
return \$n - \$d * floor(\$n/\$d);
}``````
1 Like

Libre does the same thing, and it looks like anything over .0 is rounded up for both of them. At that point the math makes sense

``````MOD(x,y) = x - (y * INT(x/y))
MOD(x,y) = (6-12) - (20 * INT((6-12)/20))
MOD(x,y) = -6 - (20 * INT(-6/20))
MOD(x,y) = -6 - (20 * INT(-0.3))
MOD(x,y) = -6 - (20 * -1)
MOD(x,y) = -6 - (-20)
MOD(x,y) = -6 + 20
MOD(x,y) = 14
``````
1 Like

yes, whereas in PHP:

``````echo intval(-0.3);
0``````

Giving you the difference in the final result. That’s why I switched it to floor(), which returns -1 for floor (-0.3);

1 Like

The definition gets a bit ambiguous when dealing with negatives. Different languages deal with it differently, so you will get this disparity in different programs/languages.

2 Likes

Thanks, guys. At least I now know why! I think I vaguely remember this as an issue when I first started programming, more years ago than I care to admit.
{"ft_lang_label":"__label__en","ft_lang_prob":0.9007272,"math_prob":0.93333733,"size":2030,"snap":"2022-27-2022-33","text_gpt3_token_len":578,"char_repetition_ratio":0.10562685,"word_repetition_ratio":0.016172506,"special_character_ratio":0.32463053,"punctuation_ratio":0.13363029,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9964986,"pos_list":[0,1,2,3,4],"im_url_duplicate_count":[null,1,null,2,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2022-06-26T04:45:46Z\",\"WARC-Record-ID\":\"<urn:uuid:6b734624-0fcc-4d9d-ba5d-40afd2afaeef>\",\"Content-Length\":\"41551\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:27d747cd-a7cf-4e9d-92b5-156962ecd9c1>\",\"WARC-Concurrent-To\":\"<urn:uuid:1546b4da-5cf3-49ca-9bc1-a2d7ea73fd8d>\",\"WARC-IP-Address\":\"13.32.151.84\",\"WARC-Target-URI\":\"https://www.sitepoint.com/community/t/modulo-differences-libreoffice-v-php/387123\",\"WARC-Payload-Digest\":\"sha1:J4EODPGLH7TLTWV3POR6V56IFGPGXGCN\",\"WARC-Block-Digest\":\"sha1:NA42PGIVOP5VGCTQDROH3STMBVIWSYWF\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2022/CC-MAIN-2022-27/CC-MAIN-2022-27_segments_1656103037089.4_warc_CC-MAIN-20220626040948-20220626070948-00186.warc.gz\"}"}