Unable to dynamically count last 10 cells of column

Hello!

My goal is to be able to automatically count the last 10 cells in a column. So far I have this:

=(COUNTIF(INDEX(AB$18:AB$182,ROWS(AB$18:AB$57)-9):INDEX(AB$18:AB$182,ROWS(AB$18:AB$57)), ">=" &J2)/10)

This returns the percentage of cells in the last 10 cells of a column that are greater than or equal to cell J2.

My issue is that this excel sheet changes extremely frequently and I would like to streamline this process by eliminating the need to change where the last row is (in this case it's 57). How can I modify this formula to recognize a change in the size of the column and automatically adjust to the location of the new 10 last values?

Is there something I can do with =IF(ISBLANK()) potentially?

Any help is greatly appreciated!

(Workbook is attached! Formula is located in cell N2).

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook. Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Administrative Note:

Is your forum profile showing the version of Excel that you need this to work for? 2202 is a release number ...

Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

Thanks.

Try:
=SUMPRODUCT(--(INDEX(B:B,AGGREGATE(14,6,ROW($B$2:$B$1000)/($B$2:$B$1000<>""),1)-SEQUENCE(10,,0))>=J2))

But... with 2211, this simplifies to:

=LET(r,B2:B1000,f,TAKE(FILTER(r,r<>""),-10),ROWS(FILTER(f,f>=J2)))

=LET(r,B2:B1000,f,TAKE(FILTER(r,r<>""),-10),ROWS(FILTER(f,f>=J2)))

red: defines the range (r)
green: filter r, returning only values where r is non-blank
Cyan defines f, the last 10 values in the range
purple filters f, returning only the rows >= J2
black returns the number of rows in f.


You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

The Outer =LET() just allows the intermediate steps to be held and resuded as needed... to make the formula much shorter.

This solution works for users EXCEL 365 from version V2203
In post #13 is a solution for all users EXCEL 365.

Please try for calculation "%save bet hit in L10" in N2 and copy down:

Formula:

Please Login or Register  to view this content.

Make sure that the values in A2:A10 match the headings of the table.

Your profile said 2211 a few minutes ago!! Didn't it??

=SUMPRODUCT(--(INDEX(B:B,AGGREGATE(14,6,ROW($B$2:$B$1000)/($B$2:$B$1000<>""),1)-SEQUENCE(10,,0))>=J2))
Red: find the highest row number that is non blank
Cyan: count back 10 rows, including the highest row
Green - return the values in those rows
Black add up the values that exceed J2.

(And divide by 10, format as % if desired...)

Here is an alternative solution for all users Excel 365 and all users Excel 2021

This formula also automatically selects the required column

Please try in N2 and copy down:

Formula:

Please Login or Register  to view this content.

Make sure that the values in A2:A10 match the headings of the table.
This formula does not need to be adjusted per row.

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.