Last value in a column
 

Good evening everybody,

Probably the question has been asked before. My problem is the following,
I'm looking to retrieve the last value in a column. And I have no formula
that is running when the values in the colums are based on a calculation and
if there a cell in this column underneath the last filled cell, that has not
yet returned its value. A clumsy solution is to suppress all the calculations
below the last calculated cell and to reenter the formula in the next cell
when necessary. Has anyone a idea how tio solve this problem?
Here the formula I use for "normal" columns
INDEX(Tab;MAX(LIGNE(Tab)*NON(ESTVIDE(Tab)))-LIGNE(Tab)+1)
in french.
INDEX(Tab;MAX(LINE(Tab)*NOT(ISEMPTY(Tab)))-LINE(Tab)+1) as a translation
with "tab" the name of the zone.

Thank you for any help.

68magnolia71

What is the 'Line' function?
Gary

"68magnolia71" wrote:

Good evening everybody,

Probably the question has been asked before. My problem is the following,
I'm looking to retrieve the last value in a column. And I have no formula
that is running when the values in the colums are based on a calculation and
if there a cell in this column underneath the last filled cell, that has not
yet returned its value. A clumsy solution is to suppress all the calculations
below the last calculated cell and to reenter the formula in the next cell
when necessary. Has anyone a idea how tio solve this problem?
Here the formula I use for "normal" columns
INDEX(Tab;MAX(LIGNE(Tab)*NON(ESTVIDE(Tab)))-LIGNE(Tab)+1)
in french.
INDEX(Tab;MAX(LINE(Tab)*NOT(ISEMPTY(Tab)))-LINE(Tab)+1) as a translation
with "tab" the name of the zone.

Thank you for any help.

68magnolia71

68magnolia71 wrote:
Good evening everybody,

Probably the question has been asked before. My problem is the following,
I'm looking to retrieve the last value in a column. And I have no formula
that is running when the values in the colums are based on a calculation and
if there a cell in this column underneath the last filled cell, that has not
yet returned its value. A clumsy solution is to suppress all the calculations
below the last calculated cell and to reenter the formula in the next cell
when necessary. Has anyone a idea how tio solve this problem?
Here the formula I use for "normal" columns
INDEX(Tab;MAX(LIGNE(Tab)*NON(ESTVIDE(Tab)))-LIGNE(Tab)+1)
in french.
INDEX(Tab;MAX(LINE(Tab)*NOT(ISEMPTY(Tab)))-LINE(Tab)+1) as a translation
with "tab" the name of the zone.

Thank you for any help.

68magnolia71

The following would return the last numeric value:

=LOOKUP(9.99999999999999E+307,A:A)

=RECHERCHE(9,99999999999999E+307;A:A)

..Hello Gary,

In the "line" (row) I may have in column B any function like
=IF(A2="";"";A1+A2) , then copied down the comlumn to A100 for example. What
I need is the value of the last filled cell in the column A. Some rows may
remain empty since there is no value in An, some cells may even have a text
written afterwards.
Hopefully it will help you to fix the problem

Anyway thaks a lot.

68magnolia71

"Gary Brown" wrote:

What is the 'Line' function?
Gary

"68magnolia71" wrote:

Good evening everybody,

Probably the question has been asked before. My problem is the following,
I'm looking to retrieve the last value in a column. And I have no formula
that is running when the values in the colums are based on a calculation and
if there a cell in this column underneath the last filled cell, that has not
yet returned its value. A clumsy solution is to suppress all the calculations
below the last calculated cell and to reenter the formula in the next cell
when necessary. Has anyone a idea how tio solve this problem?
Here the formula I use for "normal" columns
INDEX(Tab;MAX(LIGNE(Tab)*NON(ESTVIDE(Tab)))-LIGNE(Tab)+1)
in french.
INDEX(Tab;MAX(LINE(Tab)*NOT(ISEMPTY(Tab)))-LINE(Tab)+1) as a translation
with "tab" the name of the zone.

Thank you for any help.

68magnolia71

Hello Aladin,

Thank you for the formulas. I will put them in place and be back to you.
Also thanks for the translation. Not always easy and hard to find someone
with a english version of Excel. It's everything but plain english.

68magnolia71

"Aladin Akyurek" wrote:

68magnolia71 wrote:
Good evening everybody,

Probably the question has been asked before. My problem is the following,
I'm looking to retrieve the last value in a column. And I have no formula
that is running when the values in the colums are based on a calculation and
if there a cell in this column underneath the last filled cell, that has not
yet returned its value. A clumsy solution is to suppress all the calculations
below the last calculated cell and to reenter the formula in the next cell
when necessary. Has anyone a idea how tio solve this problem?
Here the formula I use for "normal" columns
INDEX(Tab;MAX(LIGNE(Tab)*NON(ESTVIDE(Tab)))-LIGNE(Tab)+1)
in french.
INDEX(Tab;MAX(LINE(Tab)*NOT(ISEMPTY(Tab)))-LINE(Tab)+1) as a translation
with "tab" the name of the zone.

Thank you for any help.

68magnolia71

The following would return the last numeric value:

=LOOKUP(9.99999999999999E+307,A:A)

=RECHERCHE(9,99999999999999E+307;A:A)