Excel: lookup the last non-zero, non-empty cell in filtered range

microsoft excelmicrosoft-excel-2010worksheet-function

The following formula gives the last visible data in a filtered range:

=LOOKUP(2,1/SUBTOTAL(2,OFFSET(R5,ROW(R5:R200)-ROW(R5),0)),R5:R200)

However, it'd return zero if the last visible cell contains zero. Anyway to filter off zero and return the last greater than zero cell?

The following is a simplified example. Both date and sport are filtered. The filters could be altered so June and basket ball are displayed, for example.

The next two columns are the balance of two currencies. I need a formula at the bottom of the currencies that displays 193 and 525 respectively. The lookup formula I found on the net displays 193 and 0, where 0 is the last number in the filtered data of a currency. What I want to display is 525, not 0. By the way, zero is not zero per se. Zero means no transaction. It actually should be blank. However, I can't make them blank because they are involved in calculation. Addition of a number to a blank gives an error.

This is for a monthly balance sheet report. I need to report 193 and 525 as the ending balances for July.

2019/07/01  tennis  775           0
2019/07/11  tennis  925           0
2019/07/11  tennis  0           125
2019/07/13  tennis  0           105
2019/07/13  tennis  0           525
2019/07/25  tennis  193           0

Another formula:

LOOKUP(2,1/(R5:R200<>0),R5:R200)

will display the last non-zero number, whether visible or not. It's not what I want either.

Best Answer

One was of accomplish this is adding your formula above into an index match formula. It would look something like this

=INDEX(R5:R200,(LOOKUP(2,1/SUBTOTAL(2,OFFSET(R5,ROW(R5:R200)-ROW(R5),0)),R5:R200),1)

I use a similar formula; INDEX(Entry20!D2:D367,(LOOKUP(2,1/(Entry20!BG2:BG367<>0),ROW(Entry20!BG2:BG367))),1) in my case column D contains the date and I'm looking for the date that the last non-zero value came up. I tried changing this to BG2:BG367 and it provided the last non-zero value.

I hope this helps, Brad

Related Solutions

Excel: How to return the last Row Number that a contains a specific string

To find earliest instance, mentioned below is the formula:-

=LOOKUP(2,1/(A:A=A2),ROW(A:A))

For your better understanding, below is the screenshot

enter image description here

Excel – Make an array formula in excel calculate only on active rows

Sadly, the use of the volatile INDIRECT and OFFSET are almost ubiquitously recommended in such cases on the various Excel sites around, even though there exists a perfectly good, minimally volatile (in fact, volatile "at workbook open" only, as I understand) set-up using INDEX.

To determine the last non-empty row in a column, column A say, it will be useful to know the datatype of the entries within that column, and also whether there are any null strings ("") present.

Although there are set-ups available which will work whatever the datatype of the entries in question, they are necessarily of a type such that they must process each element within the range passed to them. As such - and particularly if the range over which it is necessary to calculate is quite large - they can be extremely resource-heavy (the second suggestion by kyle being a good example, having to process more than a million cells, irrespective of whether the last non-empty cell is in row 1 or row 1048576).

It is also worthwhile storing the value of the last non-empty row as a Defined Name, LRow say.

The possible definitions for LRow are then as follows:

1) If there are no null strings and all entries are non-numerical, you can define LRow as:

=MATCH("Ω",A:A)

2) If there are no null strings and all entries are numerical, you can define LRow as:

=MATCH(9.9E+307,A:A)

3) If there are no null strings and entries are of a mixed datatype, i.e. some numerical, some non-numerical, you can define LRow as:

=MAX(MATCH("Ω",A:A),MATCH(9.9E+307,A:A))

If there are null strings within the range then the solutions become necessarily more complex, and it is no longer possible to avoid a construction in which each cell within the array is processed individually. As such, the best we can do is to minimize the range which we pass to be processed.

Hence, we have:

4) If there are null strings, you can define LRow as:

=MATCH(1,0/(LEN(A1:INDEX(A:A,MAX(MATCH("Ω",A:A),MATCH(9.9E+307,A:A))))>0))

(You could of course make this even more efficient if the datatype is consistently numerical or non-numerical, as above.)

which requires array-entry (CSE) and which, in general, will vastly reduce the range being processed, especially compared to something such as:

=MATCH(1,0/(LEN(A:A)>0))

which is disastrous in terms of efficiency.

Note that the additional functions within the former are of very little detriment: it is far more important to reduce the number of cells being processed than to worry about an additional function call or two.

You can now use this within an INDEX construction to dynamically define your range. For example:

=A1:INDEX(A:A,LRow)

which, as mentioned, is barely volatile at all.

Regards

Best Answer

Related Solutions

Excel: How to return the last Row Number that a contains a specific string

Excel – Make an array formula in excel calculate only on active rows

Related Question