Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel
I want to be able to always find the last non-zero value in a row of %'s with
a formula that returns that last % value.
Play with FIND() and MATCH() and see if that will get you what you want.
Typically in Excel, search functions 'prefer' sorted databases, but
there are ways to fool with that. In this case, it's annoying because
MATCH() would work fine if you could tell it to start from the right
instead of the left. (disclaimer: I'm playing w/ Excel X; newer versions
may have better sorting functions).
Here's a horrifying kludge.
assuming the data are in a14:g14, fill row 15 with the array formula
=SUM(ABS($A$14:A14))
Note that the sum will yield the same value for every column to the
right of the last nonzero entry.
Then use this formula to find the column you want:
=MATCH(MAX(A15:G15),A15:G15,0)
That will return the number of the column within your database, from
which it's not to hard to generate the value in the cell (use HLOOKUP,
or INDIRECT)
I'm sure the Excel gurus can do this a lot better.
