Free Microsoft Excel 2013 Quick Reference

Display the last non-zero value in a list of numbers

I am trying to find the last entered value in a list of basic weight data. I
enter new numeric values to the list on a daily basis, but would like to
always show the last entered value in another cell also. The list also
contains zero values, that I want to exclude or ignore from my result, e.g.


would always return 375, as the last value in the list (which will also
always be positive) it's also the VALUE I need, not the position in the list.



Post your answer or comment

comments powered by Disqus
I'm trying to find the last non zero value in a row. My data looks like this:

# J F M A M
A 4 5 5 5 5
B 0 0 3 3 3
C 0 0 0 0 2
D 0 0 3 3 0

I want to compare the earliest value to the latest (e.g. for A, that would mean 4-5 = -1; for B that would mean 3-3=0, etc).

I have a formula that finds the first non zero value in the row, but have been unsuccessful in writing one that finds the "last" non zero value in the row.

Any ideas?


I'm trying to work out how I can get a cell to pick up the last non-zero value in that row. I can move the data manually but would appreciate any ideas on how to do it more efficiently.

Any thoughts much appreciated!


I would like to know if there is a way to get the 5 largest values in a
list of numbers. I know using MAX would only get the largest. I would like
some rountine that would do it.

Thanks very much!


My dataset consists of Dynamic set of rows and columns, that means I don't know the column range and row range before hand.
I want the column number for the last non zero element in a given range in a row.And I want the operation repeated for all the rows simultaneously.
Hence I'm looking for a formula that could be copy pasted for the given range.
I'm currently using a simple loop shown below, which is obviously taking ages to execute

Note : i= column lenght
K= row length.

For j = 2 To K 
    l = i + 1 
    Cells(j, i + 7).Select 
        l = l - 1 
    Loop Until Cells(j, l).Value = 1 
    Cells(j, i + 7).Value = Cells(1, l).Value - Cells(1, 2).Value + 1 
Next j 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Hello everyone, sorry for the noob question, but I'm trying to find the the last value in a row that is greater than zero with rows that contain numbers, zeros, and blanks. I need this number so I can match it with the first non zero to calculate growth. I have the first value, and the growth rate formula, I'm just having problems with finding the last non-zero value. The function I'm using is:


Only problem is if there are blank numbers at the end of the row, it will include them and return a blank as the value.

Say the row is : "","","","",0,0,4,5,6,0,0,""

The first 4 months the publisher wasn't on contract so there are blanks, then if the pub was on contract but generated no revenue that month it's a zero, then the publisher must not be on contract anymore resulting in a blank cell at the end. I need a formula that will return "6" because that was the last amount of revenue the generated in the last month they actually generated some revenue.

Dear Everyone,

I want Excel to return my last non-zero value in the Y 08-09 column using a formula from each of the rows as shown. Please can someone help me with a formula?

Thank you very much for the help. Look forward to a quick reply.

Best Regards.

******** ******************** ************************************************************************>Microsoft Excel - Book3___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEFGHIJKLMN1Ā Apr-08May-08Jun-08Jul-08Aug-08Sep-08Oct-08Nov-08Dec-08Jan-09Feb-09Mar-09Ā YĀ 08-092PeopleĀ CountĀ 100546900249553PeopleĀ CountĀ 200249567000074PeopleĀ CountĀ 309876520000025PeopleĀ CountĀ 403214678900096PeopleĀ CountĀ 503200000000027PeopleĀ CountĀ 630002000987078PeopleĀ CountĀ 70456300000003Sheet1Ā 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

I have a row of values and I need to pull the second non-zero value on another sheet, which in the example below is $850.

$0 $0 $0 $285 $850 $278 $481

I use the Index/Match forumla below to grab the first non-zero value ($285)


This formula works great to retrieve the first non-zero value.

How can I modify this formula to grab the second non-zero value ($850)? Then how do I modify it again to grab the third non-zero value ($278)?
THen how do I modify to grab the fourth non-zero value ($481)?

I don't want to use IF/Then statement as that might be too long. I think Index/Match would do the trick, but I am missing somthing.

Any help will be appreciated!!

Can you take a look at my excel file and tell me how I can find the last non zero number in column D, Is there any other way to approach my goal in this simple worksheet? Thank you.


i have a number of large data files filled with unnecessary 0 values. some of the 0s are important (if they occur before the final non-zero item), while others are unnecessary and interfere with analysis.

the data is stored in time bins (each column is a bin)
and each row is a unique trial

what i want to do is starting at the far right, find the last non-zero value for each row and clear all of "0" values that appear to the right of that cell in the row. it seems like it should be quite simple to do in a macro, but i can't figure it out. help would be much appreciated. thanks guys!

I have data on a row for a 48 month period. There may be gaps with no data and then pick up again with some more data. The data might end before the end of the 48 month period. I need a formula to the right of the data (on same row) that will tell me the column of the last non-zero data in the row. I can number the columns for lookup purposes if need be.

The brute force method is to devote another 48 columns with a check for each month's non-zero data (return 0 or 1), cum this from the far left and find where the cum does not increase. Hideously inefficient.

Need something elegant.


In MS Excel 2007, I'm trying to use a function to search for (and display) a
specific text string that resides within a cell, providing that it is the
last non blank cell in a range of cells across a row.

e.g. If searching row 2:2 (or a section of this row) for the last non blank
cell, I want to then search within that last cell to locate the word "pass",
where the same cell may also contain any combination of text/number values.
If "pass" exists within the cell, I want to return a the same text value of
"pass". Note that ā€œpassā€ may exist across various cells in the range.

-- I've managed to successfully return the entire contents that reside
within the last non-blank cell (with the following formula):

=IF(ISNA(LOOKUP(2,1/('QA Results'!$D2:$CY2""),'QA
Results'!$D2:$CY2)),"",LOOKUP(2,1/('QA Results'!$D2:$CY2""),'QA

-- I've also achieved similar results when searching the cell range for the
last known cell that successfully contains the text ā€œpassā€ (this is not
necessarily the last non blank cell in the range). Again the entire cell
content is returned.

Results'!D2:CY2),'QA Results'!$D$2:$CY$2))),"",LOOKUP(2,1/SEARCH("Pass",'QA
Results'!D2:CY2),'QA Results'!$D$2:$CY$2))

Any assistance would be appreciated.

I have a list of dates witten sequentially in a row on the sheet. Under some
of these I have some values. I need to find the non-blank value associated
with the latest date. Is there an easy way of doing this?

How do I look up the last alpha numeric value in a column? I can get the last
numerical value just not the alpha numerical one. Can someone help?

I am trying to create a macro that will copy the last non-blank cell in a
running total column that appears on a number of worksheets to a summary
worksheet in the same workbook. Any suggestions?

Hopefully a simple question - how do you find the last non blank cell in
a column ? The trick is that the column may contain blank cells along
the way before reaching the last non blank cell.

Andrew Bourke

I am trying to average only the last 5 nonblank values in a row of
cells. Is this possible?

Hey folks,
Does anyone know if excel has a worksheet function which returns the last ligitimate (numerical) value in a list? Or will I need to use a bunch of logic...



In a formula, how can I reference the first value in a column of numbers when the row number of that first number is not constant [ie] the row number changes regularly from day to day as I insert new rows at the top of the column each day?
[and the row number is always greater than 1]

Does anyone know how to select the last 3 small values from a list? such as
a list {1,2,3,4,5,6,7,8,9}
then reture the last 3 small value {1,2,3}
Thank you for any suggestion

In excel I want to get the last 5 non zero values from a row (or column) and
get an average.

I have a row of values and I need to pull the second non-zero value on another sheet, which in the example below is $200.

$0 $0 $0 $100 $200 $300 $400

I used the Index/Match forumla below to grab the first non-zero value ($100)


This formula works great to retrieve the first non-zero value.

My question is how can I modify this formula to grab the second non-zero value ($200)? Then how do I modify it again to grab the third non-zero value ($300)?

The first value, second value, third value will be in cell A2, A3, A4 respectively.

Thanks for your help!!!

I have a series of numbers in A1:A10


I need a formula that will return the first non zero number in the series (300 in this case) regardless of it's value, then I need another formula that will return the first occurance of the next non zero number (128), and so on.

I'm trying to figure out how to get the column numbers for the first and last non-zero cells in a range. It seems pretty easy to get the actual values using some tips I found on here, but getting the actual column numbers eludes me.

Each cell represents a week, with the first non-zero being the start week and the last non-zero being the end week. All cell values are integers.

Hi all. I'm a relative newbie to using Excel and am stumped.

I'm trying to find the last (non-zero) value in a column. The cells
all have a function in them presently like =IF(C8>0,SUM(B8:C8)," ")
presently so that the cell truly is not empty. I've tried using
=LOOKUP(2,1/(D2:D350>0),D2:D350) but it displays nothing since all the
cells have a value (the function above).

How do I find the last cell having a numeric value (which is what the
first function provides)? Any help is greatly appreciated.

No luck finding an answer? You could always try Google.