Dear forum members,
Please I need a formula to count same last digit numbers and neighbouring numbers among 6 different numbers.
I have attached the sample file for your reference and also as example, I have mentioned the formula which counts same first digit numbers.
thank you for your help
Okay, is there any difference with last digit between "3+2" and "2+3"? I can easily get a formula that will give "3+2" in row 5 for example but it could just as easily give "2+3" as it doesn't sort by size. :/
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
In H4 copied down
Formula:
Please Login or Register to view this content.
In O4 (and for a similar pattern in all homogeneous numbers, i.e. 4, 33, 111)Formula:Please Login or Register to view this content.
In O5 (for no matches)Formula:Please Login or Register to view this content.
In O8 (and similar patterns where there are different permutations)Formula:Please Login or Register to view this content.
I haven't worked on the neighboring numbers yet.
Thanks ChemistB for the support.
Can you please check the result of H34, it should be 22 but the result is showing 222. please advise.
thanks
Last edited by AliGW; 07-17-2020 at 12:09 PM. Reason: Please don't quote unnecessarily!
oops, I accidentally putinto the formula twice. Remove it either from the beginning or end.Formula:Please Login or Register to view this content.
Thanks a lot ChemistB, now calculating excellent,Originally Posted by ChemistB
oops, I accidentally putinto the formula twice. Remove it either from the beginning or end.Formula:Please Login or Register to view this content.
Any chance to modify below the formula for counting neighbouring numbers as per the attachment's requirement please;
({=IFERROR(1/(1/MAX(FREQUENCY(IF((A4:E4+1)=B4:F4,COLUMN(A4:E4)),IF((A4:E4+1)<>B4:F4,COLUMN(A4:E4)))))+1,0)}
thanks
Try the following array entered formula**:Formula:Please Login or Register to view this content.
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thanks a lot JeteMc, as expected it's working perfectlyTry the following array entered formula**:Formula:Please Login or Register to view this content.
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Let us know if you have any questions.
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks