Finding non-0 value in list

Topic

New Reply

Windows XP, Excel 2007, sp2

I have a list of numbers which relate to cumulative payments per period.
Given we have 52 periods in the year,
The first few periods of the year may contain zero, and the final periods of the year (as yet) contain zero.
The values, although cumulative, may go down or up from one period to the next.
I want to find the last entered number in the list.
e.g. Typical example:
Col A, rows 1 to 52 will contain the integers 1 to 52
Col B rows 1 to 9 will contain 0
Col B rows 10 to 12 contain 23.50, 57.00, 36.50
Col B rows 13 to 52 will contain 0
The number I want from this list is 36.50 as that was the last entered value

I have no other indicator as to which period was the last period
I cannot obviosuly use MAX as the numbers may go down
Counting number of zeros is not helpful because zeros appear before and after our numbers.

any help appreciated.
Thanks
alan

Reply | Quote

Replies

If you are truly only looking for 36.50, then you can take the range B1:B52 and create a conditional formatting rule that highlights any cell in that range that contains that value. Create another rule that sets the font to white if the values are zero. That eliminates the clutter.

On the other hand, if you are really wanting to test for non-negative values, then instead of, or as well as rule one, create a rule thats test cell values for values greater than zero (or non zero).

Reply | Quote

Windows XP, Excel 2007, sp2

I have a list of numbers which relate to cumulative payments per period.
Given we have 52 periods in the year,
The first few periods of the year may contain zero, and the final periods of the year (as yet) contain zero.
The values, although cumulative, may go down or up from one period to the next.
I want to find the last entered number in the list.
e.g. Typical example:
Col A, rows 1 to 52 will contain the integers 1 to 52
Col B rows 1 to 9 will contain 0
Col B rows 10 to 12 contain 23.50, 57.00, 36.50
Col B rows 13 to 52 will contain 0
The number I want from this list is 36.50 as that was the last entered value

I have no other indicator as to which period was the last period
I cannot obviosuly use MAX as the numbers may go down
Counting number of zeros is not helpful because zeros appear before and after our numbers.

any help appreciated.
Thanks
alan

How about having the following in C1
=ROW()*(B10)
and dragging down
=MAX(C:C) will then give you the last row number containing non-zero.

The following will return the value (rather than the row number)
=INDIRECT(“B”&MAX(C:C))

Reply | Quote

Thanks Guys.
Mark, the conditional formatting works great, but I really need a value returning to be able to use that value elsewhere.
GFamily (Runcorn – just round the corner from me..almost) – that solution prompted me to try an array formula. The solution you show works great, but I unfortunately don’t have luxury of being able to add columns to the worksheet.

so far, I’ve found this to work pretty well:
given any column of numbers I can find the last entered number using the array formula:
{=OFFSET(Q1,MAX(IF(ABS(Q4:Q55)>0,ROW(Q4:Q55),0))-1,0)}

As I had negative numbers in one of the lsits I had to take an absoute view of them, then return the row number of each one in the array where greater than zero. From there I could see the maximum row number and then just use that in an offset.
My list spanned from row 4 to row 55 but I still used row 1 as the offset reference and subtracted 1 from my MAX value to get the actual row which contains the last number.

thanks for the input guys.
Alan
Warrington

Reply | Quote

You can do it without the intermediate calculations with the formula:

=INDIRECT(“B”&MAX(IF($B$1:$B$520,ROW($B$1:$B$52))))

Steve

Reply | Quote

Thanks Steve, works well as an array formula.

Reply | Quote