Results 1 to 6 of 6
Thread: Finding non0 value in list

20100628, 06:04 #1
 Join Date
 Apr 2001
 Location
 Warrington, Cheshire
 Posts
 355
 Thanks
 3
 Thanked 0 Times in 0 Posts
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

20100628, 08:19 #2
 Join Date
 Dec 2009
 Location
 Woodbury, GA
 Posts
 15
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 nonnegative 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).

20100628, 08:28 #3
 Join Date
 Feb 2003
 Location
 Runcorn, Cheshire, United Kingdom
 Posts
 372
 Thanks
 0
 Thanked 2 Times in 2 Posts

20100628, 10:03 #4
 Join Date
 Apr 2001
 Location
 Warrington, Cheshire
 Posts
 355
 Thanks
 3
 Thanked 0 Times in 0 Posts
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

20100628, 10:24 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
You can do it without the intermediate calculations with the formula:
=INDIRECT("B"&MAX(IF($B$1:$B$52<>0,ROW($B$1:$B$52) )))
Steve

20100628, 10:46 #6
 Join Date
 Apr 2001
 Location
 Warrington, Cheshire
 Posts
 355
 Thanks
 3
 Thanked 0 Times in 0 Posts
Thanks Steve, works well as an array formula.