=Last(L$22:L$50)

[bobo17]

Using Excel 2000 (9.0.2720)

Hi I am still "reverse" engineering the formulas in this DRIP (dividend reinvestment) spread sheet that someone designed, to understand how it is working.

The above formula =Last(L$22:L$50), the way I "understand" it says, check the column L rows 22 to 50 and return the last value in that colum, i.e. if the last data entered is on row 45, that data will be returned and put into the cell where the formula is. It works in the spread sheet.

My problem, I am trying to "reproduce" these formulas to understand them and I use the =Last comand and it returns the error message: #Name? If it works in the spreadsheet I got why does it not work when I try to "reproduce" it in a new spreadsheet, in other words the version of excel i am using correctly executes the formula on the spreadsheet i got from a web site, but does not seem to work when I try to reproduce it.

Do i understand the formula correctly? and why does it not work when trying to reproduce.

thanks
bobo

 

[Norie]

Last isn't a built-in function as far as. I know.

It could be from of an add-in or more likely a simple UDF.

Is there any code in the workbook?

 

[T. Valko]

Using Excel 2000 (9.0.2720)

Hi I am still "reverse" engineering the formulas in this DRIP (dividend reinvestment) spread sheet that someone designed, to understand how it is working.

The above formula =Last(L$22:L$50), the way I "understand" it says, check the column L rows 22 to 50 and return the last value in that colum, i.e. if the last data entered is on row 45, that data will be returned and put into the cell where the formula is. It works in the spread sheet.

My problem, I am trying to "reproduce" these formulas to understand them and I use the =Last comand and it returns the error message: #Name? If it works in the spreadsheet I got why does it not work when I try to "reproduce" it in a new spreadsheet, in other words the version of excel i am using correctly executes the formula on the spreadsheet i got from a web site, but does not seem to work when I try to reproduce it.

Do i understand the formula correctly? and why does it not work when trying to reproduce.

thanks
bobo

That function is not a native Excel function.

You can do the same thing with built-in functions but how to structure the formula depends on the data type of the range of interest.

Is the data text? Numeric? Could be either? Are there any formulas in the range that return formula blanks ("")? Are there any empty cells within the range?

 

[snoopyhr]

this one way to locate last entry (whether Number or text) in a column, hope it helps..

Excel Workbook
	A	B	C	D
1	Numbers	Names	*	*
2	12	Peter	The last entry in Col A is :	92
3	18	Mike	The last name entry in Col B *is:	Kilder
4	23	Joe	*	*
5	4	Brian	*	*
6	5	Mike	*	*
7	56	Jason	*	*
8	92	Kilder	*	*
Sheet5

 

[Aladin Akyurek]

this one way to locate last entry (whether Number or text) in a column, hope it helps..

Excel Workbook
	A	B	C	D
1	Numbers	Names	*	*
2	12	Peter	The last entry in Col A is :	92
3	18	Mike	The last name entry in Col B *is:	Kilder
4	23	Joe	*	*
5	4	Brian	*	*
6	5	Mike	*	*
7	56	Jason	*	*
8	92	Kilder	*	*
Sheet5

The idioms are designed to avoid calls to COUNT and COUNTA, which miss in-between blank/empty cells. Also, they are used to calculate fast. The way you use them here do not follow those intents.

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

returns the last numeric value fom A.

=LOOKUP(REPT("z",255),B:B)

returns the last text value from B.

Both are regular, non-array formulas.

Post #7 in the following link explains the mechanics of these formulas:

http://www.mrexcel.com/forum/showthread.php?t=310278

 

[T. Valko]

That function is not a native Excel function.

You can do the same thing with built-in functions but how to structure the formula depends on the data type of the range of interest.

Is the data text? Numeric? Could be either? Are there any formulas in the range that return formula blanks ("")? Are there any empty cells within the range?

To return the last numeric value from column A:

=LOOKUP(1E100,A:A)

To return the last text value from column B:

=LOOKUP("zzzzz",B:B)

 

[bobo17]

The cell contains a numeric value (number of current shares owned) This is the formulas that is in each cells of the column L:

=IF(AND(B22="",D22="",F22="",K22=""),"",ROUND(SUM(B$22:B22)-SUM(F$22:F22),12))

The column purpose is to show the progressive number of shares owned and I believe this function checks to see the last "non blank" entry and return it to the cell with the "=Last" command.

Since the spreadsheet has many rows that are blank (i.e. no transactions buying shares yet, it looks for the last number in the column and this is the "latest" number of shares owned.

Is there a way to show "hidden" formulas on the spreadsheet, that may relate to this command (i.e. if it is a home made command, how would I find where the construction of the command is on the spreadsheet)?

thanks
bobo

 

[moonfish]

You can look at user defined functions by opening your VBA editor (Alt+F11) and open the available modules (scroll down in the top left corner of the window).

 

[bobo17]

Thanks (I think)
you have opened a whole new area for me to explore.
I would be willing to send the sheet to someone if they wanted to see it for themselves and help me out with understanding it etc., but it is not necessary, I will continue to plug away and learn.

I am doing this not because the spreadsheet is "not working", but to understand the embedded formulas, so that in say 5 years if there is a problem, I will hopefully by that time be able to fix it.

bobo

 

[Norie]

Have yoy checked if there is any code?

You could actually see if Last is a UDF by going to Insert>Function...

You should see various categories of function listed or in q dropdown.

One of them should be called User-defined functions.

Select that and see if anything is listed.