counting date in excel sheet

[kenguru]

Hi,

Is it possible to calculate which is the last line in an excel sheet which contains date, using excel formula? I done this with VB script.

For ex:
A1= 1
A2 = 2
A3 = 4
A4= nothing
A5=6

.... nothing lower ...

And as a result i would need the A5 cells, because that contains the last data.

Thank you

 

[JoanneM]

Hello Ken

could I clarify somthing
Are you trying to find
a) the last line containing data
or
b) the last line containing a date

I am assuming it is b because of the title but it would be good to know which you want as you say date and then data

jo

 

[kenguru]

Sorry for not specifying clearly my problem.
I need the last line which contains data (=text).

 

[SkipVought]

Hi,

There is no formula.

There is a feature ctr+END selects the cell in the last row/column.

If you had a VALUE or a FORMULA in each cell in a column, you could use the COUNTA function. But you seem to have empty cells.


Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[kenguru]

I need to know which line contains data, because I have to create the following formula:

C100 = Sum(C1:C50)+ .... - where let say that C50 is the last line containing text. I have to calculate the sum of the lines.

But if on the sheet there are inserted new lines, than there will be more than 50 lines and the formula won't be correct.

So I need something which will tell me which is the list line which contains data.

 

[AnotherHiggins]

This array formula will find the last populated cell:

=MAX(IF(NOT(ISBLANK(A1:A[blue]100[/blue])), ROW(A1:A[blue]100[/blue]),0))
[tab](change the row to a number greater than you will ever encounter)

If you are looking in more than one column, that's no problem - just change the references:

=MAX(IF(NOT(ISBLANK(A1:C100)), ROW(A1:C100),0))

[!]NOTE[/!]: To enter an array formula, you must press [Ctrl]+[Shift]+[Enter]. Simply pressing enter will result in the formula not working correctly.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

I just saw your last post, kenguru. You seem to be putting totals at the bottom of a column of data. Why make your users go looking for the sum?

Place the formula at the top of the page where it is always easily found and just account for all rows:

In A1: [COLOR=blue white]=Sum(A2:A65536)[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[yogia]

Hi kenguru:

If you are trying to find the last numeric entry in column A, then you can use the following formula ...

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

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[yogia]

Hi kenguru:

And if you are trying to find the row number which contains the last numeric entry in column A, the you can use ...

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

and for the address of the cell containg the last numeric entry in column A

=ADDRESS(MATCH(LOOKUP(9.99999999999999E+307,A:A),A:A),1,4)

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[JoanneM]

Hello again

Have the suggestions resolved the issue for you?
If not I wrote a solution yesterday but if it's resolved I'll leave you to your work.


Jo

 

[kenguru]

Yes this solved my problem.

Thanks a lot for your answers !

 

[SkipVought]

Yogi,

I'm sorry, but I get unreliable results when a numeric value is repeated in the column.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[yogia]

Yogi,

I'm sorry, but I get unreliable results when a numeric value is repeated in the column.

Hi Skip:

The formula I posted should pickup the last numeric entry in the referenced column.

If there is more than one entry with the same value in a column (or a range for that matter), it is in matching the row number, one has to decide whether one wants to pick up the first matching entry, the last matching entry, or the nth entry from the beginning or the end.

If you would post an example where you got conflicting result(s), we can discuss it further.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[SkipVought]

Formula in B1, returns A8, the highlighted address.
[tt]
A B
=ADDRESS(MATCH(LOOKUP(9.99999999999999E+307,A:A),A:A),1,4)


1
2

4
[highlight]2[/highlight]

5

2
[/tt]

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[yogia]

Hi Skip:

Even though for the example data you used, it gave me the correct result, I have to agree with you that the formula is not robust. In cell D4 of the following illustration, I offer a robust formula, however, I have to use a specific range in this case instead of the entire column ...

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com