LinkBack URL
About LinkBacks
Hello,
Can anyone help, I want to get all the divisors of a number in excel, That number is not constant, it keep changes, I want a formula to get the divisors of the number which updates automatically.
thanks and Regards
Hello,
Can anyone help, I want to get all the divisors of a number in excel, That number is not constant, it keep changes, I want a formula to get the divisors of the number which updates automatically.
thanks and Regards
Hi, welcome to the forum
How would you do this manually, and what would a sample answer look like?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hi, Thanks
If A1 = 12, then adjacent cells or A2 to A (N) should show the divisors of the A1,
and if A1 changes then adjacent cells should also change...
Thanks and Regads
If your number is in A1 then put this in C1 and enter it as an array formula (Cnt + shift + enter) then drag it across say 20 columns. if you want really big numbers you'll need to drag it across more columns AND you'll need to expand the formula to use more columns to work out the divisors
Please Login or Register to view this content.
Last edited by Crooza; 12-27-2015 at 04:26 AM.
Happy with my advice? Click on the * reputation button below
I am getting this message while trying to paste the code in C1, and If I click OK and clicking CTRL+SHIFT+ENTER, nothing happening....Screenshot_31.png, please help
Now I m getting all 1 s in the cells, Screenshot_32.png
Last edited by sam_thesupreme; 12-27-2015 at 04:34 AM.
Now I am getting some result, but I think it is not right, i have to get all the Factors / Divisors of the given numberScreenshot_33.png, I dragged the formula to 50 coloums but numbers are not coming in full
Hi,
Got it finally, Thank you....
is it possible to get the values in vertical rather than in horizontal...Thanks
Try this amended version of Crooza's formula
=IFERROR(INDEX(SMALL(IF(MOD($A$1,ROW($A$1:$A$100))=0,ROW($A$1:$A$100),""),ROW($A$1:$A$100)),ROW($A3)-2),"")
Array entered
Life's a spreadsheet, Excel!
Say thanks, Click *
I am sorry to say, this following formula worked for me to get all the Divisors, but I wanted it in Vertical...
=IFERROR(SMALL(IF(MOD($A2,ROW(INDIRECT("1:"&$A2)))=0,ROW(INDIRECT("1:"&$A2))),COLUMNS($B2:B2)),"")
Originally Posted by sam_thesupreme
Did you try ace_xl's version above?
I dont know the version sir, I just found the formula somewhere, and applied it, and it worked, but horizontally, just wanted it to work vertical...
Thanks
Ace_xl posted an amended version of my formula just above your post. It changes columns to rows so you can do the same thing. Copy it, enter as array formula by pressing cntr, shift, enter then drag down however many rows you want
This is the vertical version
Please Login or Register to view this content.
Thank you sir..
Hello,
Can I get Divisors / Fractals of a Decimal Value like 120.25 etc.... Thanks and Regards
Normally factors are integers. So if we used your example above you'd want 0.25 and 481 are factors of 120.25!? What level of fraction are you wanting to go down to? 0.125 and 962 are also factors if you keep going. Where does it end?
It should end near 1.0 / 0.5, is it possible sir ?
Short version of Crooza's formula from post #13
Formula:
Please Login or Register to view this content.
Not so short version for decimal values, this will return factors where the number of decimal places is less than or equal to the number of decimal places in the original value.
Formula:Please Login or Register to view this content.
Note that both of these formula need to be array confirmed as before. The formula for decimals will also work correctly with integers (non-decimal values) so you won't need to change the formula for different values.
Thanks a lot sir, Its working.... Its great feeling to have your support here...Thank you...
Hello sir,
the formula is doing gud, but it is not calculating for some entries which contains 4 digits after decimal.... 281.9072, 845.7216,1691.4432 etc etc....kindly look into this.
Thanks and Regards
It is working upto 6 Digits in total, if 3 Digits after Decimal (like 385.254: its working, but 385.2841 its not working ), its working for number like 3456.12, but not for 3456.124, plzz help
With the type of formula required to perform this task, there is a limit to the permissible values, which you have exceeded.
With no decimal, the limit is 1048576
With 1 digit after the decimal, the limit is 104857.6
With 2 digits, the limit is 10485.76
With 3 digits, the limit is 1048.576
With 4 digits, the limit is 104.8576
With 5 digits, the limit is 10.48576
There is no alternative formula method that I know of that would enable you to work around the limits.
Its not working for 104.8576 also sir...
you have mentioned 7 digits in total, but its only working for 6 digits in total
104.8576 does work unless you're using a really old version of excel (2003 or older) in which case, the limits would be
65536
6553.6
655.36
65.536
6.5536
sorry, i mis interpreted the number with the digits.... its working for 104.8576 but not above that if 4 digits...105.8576.... thanks, and I m using Office 2013...
What is the highest number of digits you would need to work with (before and after decimal)?
I need to work with max 9999 before decimal and 9999 after decimal, Thanks and Regards
What is the divisor of a number with decimals, in your opinion?
For Example, 8545.254 gets 8545.254 itself, and 4272.627, 2136.314, 1068.157 etc upto max 0.5 / 0.25
for 85.4654
we get
0.0001
0.0002
42.7327
85.4654
Thoughts:
1) We are working with decimals, so we are going to bump up against some of the limitations of computer arithmetic. For example, using the proposed =MOD() function to test for "is this a divisor", =mod(85.4654,0.0001) returns -1.6E-15 when I perform that calculation, which is not "exactly equal to 0" which means the test =MOD(85.4654,0.0001)=0 is going to fail to detect 0.0001 as a divisor of 85.4654. Whatever algorithm you use, it is going to need account for the limitations of binary arithmetic.
Since I know that there are many integer factorization algorithms out there, I would likely be inclined to multiply my input number by 1E4 and force that result to an integer. Then apply the desired integer factorization algorithm to the problem, then divide the results by 1E4 to recover the original values.
2) It appears that you will potentially be working with up to 8 digit numbers. Based on a quick look at the proposed formulas, it appears that everyone is focusing on a "trial division" type algorithm. It is an effective algorithms, but it is among the least efficient of factorization algorithms. If it works well enough, then use it, but it might be worth researching other, more efficient factorization algorithms https://en.wikipedia.org/wiki/Intege...ing_algorithms
3) It might be obvious, but an 8 digit number potentially has a lot of possible divisors/factors. I don't know what the theoretical maximum is, but it could be large.
I don't know how much of that helps. I suspect that the main problem is in thought 1 -- the limitations and inaccuracies in binary arithmetic. Your problem might work out just fine with existing formulas if you adapt them to account for these inaccuracies ("is ABS(MOD(number, factor))<1E-10" as your logical test instead of "is MOD(number,factor) exactly equal to 0").
Hi guys,
Sorry been asleep but I see you guys have had some good discussion
I'd simply use this for divisors that are fractions
=IFERROR($A$2*INDEX(SMALL(IF(MOD($A$1/$A$2,COLUMN($A$3:$CV$3))=0,COLUMN($A$3:$CV$3),""),COLUMN($A$3:$CV$3)),COLUMN(C3)-2),"")
it requires using A2 cell to set the fraction. say 0.5 or 0.25. if you want to use 0.3333333 enter it into cell A2 as =1/3 so you get the right precision
Sorry I did it on my original column version. I'm sure you'll work out the logic for the row (vertical) version
Sorry to say, the above formula is giving Multiplications, not Divisors..
Thanks and RegardsScreenshot_37.png
Not sure how you're getting that result with my formula.
Try some easy ones
a1= 7.5
a2 = 0.5
Results should be 0.5, 1.5, 2.5, 7.5
If you get this the the formula is right. You can try some bigger examples after that
Last edited by Crooza; 12-28-2015 at 01:44 AM.
The shortened version of Crooza's latest formula, adjusted for vertical use.
Original value in A1, desired factor in A2, formula in A3 and filled down.
=IFERROR(A$2*SMALL(IF(MOD(A$1/A$2,ROW(A$1:A$100))=0,ROW(A$1:A$100)),ROWS(A$2:A2)),"")
Note that this done to return the same results as the original in post #32, I haven't looked at the results it produces. Given the problems we have already encountered when the starting number in A1 exceeds the boundaries that I mentioned in post #22, I think that alternative thinking is required. (see MrShorty's thoughts in post #31).
Hello sir,
All things sorted out,, Thank you very much for your help.
Great. Glad we got there
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks