LinkBack URL
About LinkBacks
Hi --
I'm hoping to return the row number (in range) of the LAST occurrence of the MIN value of a range of numbers that satisfy a criteria.
I've attached a sample workbook which I hope helps explain further (see groups 1 & 4).
Thank you!
Hi --
I'm hoping to return the row number (in range) of the LAST occurrence of the MIN value of a range of numbers that satisfy a criteria.
I've attached a sample workbook which I hope helps explain further (see groups 1 & 4).
Thank you!
Give this formula a try. In G5, enter =SUMPRODUCT(MAX(ROW($C$5:$C$164)*($C$5:$C$164=MINIFS($C$5:$C$164,$D$5:$D$164,D5)))) and fill the formula.
You can use this array* formula in G5:
=IF(ISTEXT(D5),"",MAX(IF($C$5:$C$164=MIN(IF($D$5:$D$164=D5,$C$5:$C$164)),ROW($C$5:$C$164))))
*Note that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual Enter.
Then you can copy the formula down.
Hope this helps.
Pete
As 1st: do you want to row number OR number from No. column? According to your manually entered figures number from No. column.
2nd: I do not know why you've marked last occurrence of MIN on No. 23 if MIN for group 1 appears only once in No. 13, so 1st and last occurrence is the same.
Anyway, put into G5:
Formula:
Please Login or Register to view this content.
and drag it down.
Only for group 4, I think there is 2 occurrences of MIN.
Capture.PNG
Last edited by KOKOSEK; 08-11-2021 at 05:39 AM.
Happy with my answer * Add Reputation.
If You are happy with solution, please use Thread tools and mark thread as SOLVED.
Please try
Top down
=IF(N(D5),INDEX(SORT(FILTER($B$5:$C$164,$D$5:$D$164=D5),2),1,1),"--")
BOTTOM UP
=IF(N(D5),INDEX(SORT(FILTER($B$5:$C$164,$D$5:$D$164=D5),{2,1},{1,-1}),1,1),"--")
Thank all!!
The various solutions worked!
Last edited by xtinct2; 08-11-2021 at 06:52 AM.
Glad to hear that, and thanks for marking the thread as Solved.
You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks