• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Inverse function of MOD

B

bines53

Active Member
Hello friends,

This function =MOD(87,8),87 is number,8 is divisor ,the result is a remainder equal to 7.

The question is, if I knew what remainder , and the number, what is the formula to get the divisor ?

Thank you !
 
Hi David,
suppose A1 = 8
and B1 = 87

try this:
=MATCH(1,INDEX(MOD(ROW(INDIRECT("1:"&A1))*B1,A1),0),0)

I found the solution here [Barry Houdini]

Regards,
 
Hi Deepak,

The formula does not work in different cases, for example,
Remainder=2
Number =50
Divisor =4
The result is that your formula, 3


Thank you !
 
Hi @bines53

You must be clear about the answer you want. For Example:

Number = 50
Remainder = 2

Have many answers for the divisor:
3, 4, 6, 8, 12, 16, 24, 48.

Which one is correct for you?

Blessings!
 
Hi David ,

Given a numerator of 50 and a remainder of 2 , you can have all sorts of divisors !

3 , 4 , 6 , 8 , 16 , 48

will all give a remainder of 2 when they are used to divide 50.

The following formula will return 3 , the minimum divisor.

=MIN(IF(MOD(50,ROW(2:50))=2,ROW(2:50)))

Enter this as an array formula , using CTRL SHIFT ENTER.

Narayan
 
Hi friends,

Although I gave examples with simple numbers, however I would like to receive all the possible outcomes, with no volatile functions.
That is all that,3, 4, 6, 8, 12, 16, 24, 48.

Thank you !

 
Hi John ,

perfect !!!

John ,last night, I made something different, more mathematical, I get only the minimum value, you think there's a way to get all seven other values?

=(B1-MOD(-(B1*B2),B1-B2))/B2

Thank everyone for the help !
 
Hi again @bines53!

Your formula doesn't work for all numbers (try 70 and 2, for example).

The dividend depends of three factors: Quotient, divider and remainder. If you have two of them, you could get a single solution or multiple solutions, depends on the number. The formula is:

D = d * Q + r
D : Dividend
d : divider
Q : Quotient
r : remainder

Check file for understanding. Blessings!
 

Attachments

  • Ex.xlsx
    10 KB · Views: 13
For a formula that returns all results {3;4;6;8;12;16;24;48} you can try:

=MODE.MULT(IF(MOD(50,ROW(2:50))=2,ROW(2:50),{"",""}))

If A1=50 and A2=2 you can change the 2:50 to INDEX(A:A,2):INDEX(A:A,A1) and the 2 to A2 as in John Jairo V's link. With this replacement the formula will recalculate at start up or whenever a value in column A changes.

More of theoretical interest, but for a truly non-volatile solution maybe try (versions 2013/16):

=MODE.MULT(IF(MOD(A1,INT(TREND(2*INT((2*A1)^0.5+1)^2+1^MUNIT((2*A1)^0.5+1),,,0)/6)+1)=A2,INT(TREND(2*INT((2*A1)^0.5+1)^2+1^MUNIT((2*A1)^0.5+1),,,0)/6)+1,""))
 
Last edited:
Hi Lori,

Thank you ,I'm still with Excel 2010 !

This method INDEX(A:A,2):INDEX(A:A,A1) ,There is a limit which, if the number (Dividend) is greater than one million, does not work.
It is also possible to limit the number of answers you're looking for, in this way
=SUMPRODUCT(--(MOD(B1,N2:N4)=B2)) ,when N2:N4 ,4,16,16.


David
 
Last edited:
You must log in or register to reply here.
Back
Top