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?

Hi Simon

(BTW you can't have 31 nested IFs ... only 7)
however, this formula should give you what you need:
=INDEX(E1:E10,MATCH(9.99999999999999E+307,E1:E10))

where E1:E10 is the range of your balances.
For an explaination of how this formula works check out
http://www.xldynamic.com/source/xld.LastValue.html

Cheers
JulieD

"Simon Shaw" wrote in message
...
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?

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.

"JulieD" wrote:

Hi Simon

(BTW you can't have 31 nested IFs ... only 7)
however, this formula should give you what you need:
=INDEX(E1:E10,MATCH(9.99999999999999E+307,E1:E10))

where E1:E10 is the range of your balances.
For an explaination of how this formula works check out
http://www.xldynamic.com/source/xld.LastValue.html

Cheers
JulieD

"Simon Shaw" wrote in message
...
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?

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

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

For a very large range, this would be expensive.

Simon Shaw wrote:
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.

"JulieD" wrote:


Hi Simon

(BTW you can't have 31 nested IFs ... only 7)
however, this formula should give you what you need:
=INDEX(E1:E10,MATCH(9.99999999999999E+307,E1:E10 ))

where E1:E10 is the range of your balances.
For an explaination of how this formula works check out
http://www.xldynamic.com/source/xld.LastValue.html

Cheers
JulieD

"Simon Shaw" wrote in message
...

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?

"Aladin Akyurek" wrote...
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)
....

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)

?

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...!

"JulieD" wrote:

Hi Simon

(BTW you can't have 31 nested IFs ... only 7)
however, this formula should give you what you need:
=INDEX(E1:E10,MATCH(9.99999999999999E+307,E1:E10))

where E1:E10 is the range of your balances.
For an explaination of how this formula works check out
http://www.xldynamic.com/source/xld.LastValue.html

Cheers
JulieD

"Simon Shaw" wrote in message
...
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?

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:A80,ROW(A1:A8)),1))

Biff

-----Original Message-----
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...!

"JulieD" wrote:

Hi Simon

(BTW you can't have 31 nested IFs ... only 7)
however, this formula should give you what you need:
=INDEX(E1:E10,MATCH(9.99999999999999E+307,E1:E10))

where E1:E10 is the range of your balances.
For an explaination of how this formula works check out
http://www.xldynamic.com/source/xld.LastValue.html

Cheers
JulieD

"Simon Shaw"
wrote in message
news:4B5FCE43-D508-4B4E-A66E-
...
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?



.

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

"Biff" wrote:

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:A80,ROW(A1:A8)),1))

Biff

-----Original Message-----
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...!

"JulieD" wrote:

Hi Simon

(BTW you can't have 31 nested IFs ... only 7)
however, this formula should give you what you need:
=INDEX(E1:E10,MATCH(9.99999999999999E+307,E1:E10))

where E1:E10 is the range of your balances.
For an explaination of how this formula works check out
http://www.xldynamic.com/source/xld.LastValue.html

Cheers
JulieD

"Simon Shaw"
wrote in message
news:4B5FCE43-D508-4B4E-A66E-
...
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?



.

Hi!

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

=INDEX(A1:A8,LARGE(IF(A1:A80,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

-----Original Message-----
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

"Biff" wrote:

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:A80,ROW(A1:A8)),1))

Biff

-----Original Message-----
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...!

"JulieD" wrote:

Hi Simon

(BTW you can't have 31 nested IFs ... only 7)
however, this formula should give you what you need:
=INDEX(E1:E10,MATCH(9.99999999999999E+307,E1:E10))

where E1:E10 is the range of your balances.
For an explaination of how this formula works check
out
http://www.xldynamic.com/source/xld.LastValue.html

Cheers
JulieD

"Simon Shaw"
wrote in message
news:4B5FCE43-D508-4B4E-A66E-
...
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?



.


.