finding the last value

S

Simon Shaw

I have a monthly inventory sheet, it has an opening balance column, a couple
of columns for different activity and an ending balance - each row is a day
of the month. Untill today() = the row date (column A), the ending balance
column remains zero.

I have a total row at the bottom for the activity columns, but for the
ending balance column I would like to be able to display the last balance. I
have thought about an IF statement to check each cell until I find a value,
but 31 nested IF's sounds a little crazy.

Ideas?
 
S

Simon Shaw

this works so long as the formula within the range is not returning a zero
for the remaining amounts...

34
25
57
67
48
0
0
0

the formula returns zero.
 
S

Simon Shaw

I ended up adding a column that checked if the number in the next row was
zero and the current row was not zero. If true return a 1, false return 0.
Then I used a sumproduct of the two columns.

but your example is interesting... that sure is thinking outside the box...!
 
B

Biff

Hi!

No need for helper columns.

Try this formula entered as an array with the key combo of
CTRL,SHIFT,ENTER:

=INDEX(A1:A8,LARGE(IF(A1:A8>0,ROW(A1:A8)),1))

Biff
 
S

Simon Shaw

Hi Biff,

I get a #REF error when I use the formula...

I think the LARGE formula requires another parameter, but not sure what to
use...

Thanks
 
B

Biff

Hi!
I get a #REF error when I use the formula...
Click to expand...

=INDEX(A1:A8,LARGE(IF(A1:A8>0,ROW(A1:A8)),1))

It's probably due to the range that you are using in the
ROW() function.

It's not the same as the range reference that is used for
the INDEX argument.

The range used in the ROW function is equivalent to the
total number of values in your range, not the actual
physical location of the range itself.

INDEX(A1:A8.....

That range has a physical location of A1:A8 and has a
total of 8 positions. When Excel calculates this formula
it creates a virtual array of the values in the range
A1:A8. Where A1 is the 1st position, A2 the 2nd position,
A3 the 3rd position and so on.

The ROW function is simply a means that is used to tell
Excel which POSITION in the virtual array to find the
value we're looking for.

Consider this example:

INDEX(A100:A110....

The physical location of this range is A100:A110. This
range contains a total of 10 positions. Where A100 is the
1st position and A110 is the 10th position.

In this case the ROW function argument would be ROW
(A1:A10).

Biff
 
A

Aladin Akyurek

You appear to be looking for the last non-zero value...

=LOOKUP(2,1/(ABS(E1:INDEX(E1:E10,MATCH(9.99999999999999E+307,E1:E10)))>0),E1:E10)

For a very large range, this would be expensive.
 
H

Harlan Grove

Aladin Akyurek said:
You appear to be looking for the last non-zero value...

=LOOKUP(2,1/(ABS(E1:INDEX(E1:E10,MATCH(9.99999999999999E+307,
E1:E10)))>0),E1:E10)
Click to expand...
....

I haven't tested, but LOOKUP doesn't mind if its 2nd and 3rd arguments are
different size? Why would this be better than the simpler

=LOOKUP(2,1/(-E1:E10<>0),E1:E10)

?
 
Top