Selecting range from bottom visual cell containing a value in a column upwards

[19jack91]

Hi all, I'm hoping someone is able to advise if there is a formaula able to select a range of cells starting from the very bottom cell in a column, which contains a value, and is visible within a filtered column, up 25 cells. This is so that a formula I have only looks in that range, which is from the bottom cell to the 25th cell above in a single row. To give a bit more information, I have an existing fomula that inputs a range of cells as one of its parameters. However, I'm looking for that range input to be be from the very last value in a column to (and including) 25 cells up towards the top. Is this possible? Thanks in advance.

 

[jasonb75]

Welcome to Mr Excel

Do all 25 cells have to be visible, or just the bottom one?

What you ask should be possible but it will need some (possibly quite complex) volatile formulas, with a large amount of data this could equate to slow calculation times.

 

[jasonb75]

How you go about this would also depend on what you want to do with that range once it has been identified, a simple SUM or COUNT would not be too difficult once the range is identified, anything beyond that could be close to impossible.

 

[19jack91]

Thank you so much for your quick reply! So I have a spreadsheet where I filter a couple of columns, then look at another column (BQ) to work with the values that are subsequently presented there. My original intention was to find a formula that calculated the number of visible rows in this BQ coulmn that had a value greater than 0.1. I ended up finding a solution which led to the creation of this somewhat complex formula:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(BQ3:BQ50000,ROW(BQ3:BQ50000)-MIN(ROW(BQ3:BQ50000)),,1)),--(BQ3:BQ50000>0.1)).
I have to admit I don't fully understand all of its variables, but it did the job. The amount of rows changes depending on what is filtered, and can appear in any row number between this quite large range of BQ3:BQ50000. Within this range is where I'm trying to have only the last 25 visible cells of the BQ column selected, but the row locations of the cells can be in literally any of the rows in the BQ3:BQ50000 range. And I'm only looking to get the last 25 cells that are visible, so not including any filtered out rows between each of these cells. I hope that makes sense, and thanks again for your help.

 

[jasonb75]

I ended up finding a solution which led to the creation of this somewhat complex formula:

If you think that was complex you might want to close your eyes

This is still counting the number of rows in BQ with a value >0.1, but is limited to the last 25 visible rows. I've assumed that there will always be at least 25 visible rows, if there are less then the formula will result in a #NUM! error. This can be fixed, but the method of doing so would depend on whether the count should be done on the available rows or void.

=SUMPRODUCT(--(SUBTOTAL(9,OFFSET($BQ$3,ROW(INDEX($BQ:$BQ,AGGREGATE(14,6,ROW($BQ$3:$BQ$50000)/SUBTOTAL(3,OFFSET($BQ$3,ROW($BQ$3:$BQ$50000)-1,)),25)):INDEX($BQ:$BQ,AGGREGATE(14,6,ROW($BQ$3:$BQ$50000)/SUBTOTAL(3,OFFSET($BQ$3,ROW($BQ$3:$BQ$50000)-1,)),1)))-1,,1,1))>0.1))

 

[19jack91]

Wow, that's absolutely incredible! It worked perfectly Thank you so much, I would never have been able to figure that all out!