LinkBack URL
About LinkBacks
Dear Excel Experts,
Can someone please help me in providing a formula that counts multiplicative/divisible pairs and consecutive numbers from rows.
I have attached the file for easy reference
Thanks in advance
Counting multiplicative/divisible pairs and consecutive numbers from rows
Dear Excel Experts,
Can someone please help me in providing a formula that counts multiplicative/divisible pairs and consecutive numbers from rows.
I have attached the file for easy reference
Thanks in advance
Some possible options, if I've understood the requirement - please refer "notes" re: differences versus your expected results.
Formula:
Please Login or Register to view this content.
Last edited by XLent; 06-29-2020 at 09:03 AM.
I have changed the 'multiplicative pairs' approach, below, to avoid false positives (I hope); I have also modified the others to include a handler, of sorts.
Formula:Please Login or Register to view this content.
Thanks for a quick response. looks there is an error on counting first digits. For instance, when i type numbers like 13, 14,15, 222, 2222, 22222, 258 and the result is showing 6 instead of 4.
Thanks you
Last edited by AliGW; 07-01-2020 at 07:10 AM. Reason: Please don't quote unnecessarily!
Attach a workbook that shows the error so that we can troubleshoot.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
try below --- dawned on me that the test for strings of len 2:15 is pointless as you need only test left most character
Formula:Please Login or Register to view this content.
thanks, working but taking very long time if the rows are more than a million. is it possible to modify the following formula which is without array; =IFERROR(SUMPRODUCT(--(LEFT($C21:$I21,1)+0=MODE(LEFT($C21:$I21,1)+0))),"")
Last edited by AliGW; 07-01-2020 at 08:57 AM. Reason: Please don't quote unnecessarily!
SUMPRODUCT is no more efficient than an Array (sometimes less so), and to do what you want your best option is to use FREQUENCY.
Of course, if you're running these calcs over a million rows then it's debatable as to whether or not you should be using XL in first instance.
Regardless, assuming you continue with XL, to do this efficiently you would be best served using helper matrices - this would allow you to avoid Array type calculations altogether.
fear enough, thanks a looooot for the effort and great support
Last edited by AliGW; 07-01-2020 at 08:57 AM. Reason: Please don't quote unnecessarily!
Administrative Note:
Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!
For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks