FAQ - Last Number

PROGRAMMING MANAGEMENT		A resource for users and developers of Microsoft Excel	Site Map	Search


			Email us your FAQ suggestion.

			Visit often ! Pages are updated daily.
	This is the Excel "How to" Frequently Asked Questions page
Answers to Frequently Asked Questions about how to do common tasks in Microsoft Excel.

The World of Atlas

Home

Excel Solutions and Examples

Access & Excel	Add-Ins	API	Charts	Class Modules	Comments	Conditional Formatting	Control Toolbox
Data Validation	Date & Time	Dialog Sheets	Edit	Errors	Events	Excel Fact & Fiction	Excel Object
Filter	Find / Replace	Format	Forms Toolbar	Formulas	Glossary	Hyperlinks	Internet & Excel
Keyboard	Menu Bar	Mouse	OLAP Cubes	Outlook & Excel	Pivot Tables	PowerPoint & Excel	Print
Ranges	Ribbon	Sheets	Sort	SQL	Tips & Tricks	User Defined Functions	UserForms
VBA & the VBE	VBA Magic	Window	Windows	Word & Excel	Workbooks	XLM "4.0 Macros"	XML

Excel Community

Excel "How to" FAQs

List of Excel "How to" FAQs with links to their answers.

Data entry: How to...		Formulas for sum, count, lookup, last entry: How to...		Worksheets and workbooks: How to...
	...identify duplicate entries in a range?		...return the last number and its cell address?		...display the worksheet tab name in a cell?
	...prevent duplicate entries in a range?		...return the last text and its cell address?		...display the workbook name in a cell?
	...delete duplicate entries in a range?		...return the last any type entry and its cell address?		...display the workbook path in a cell?
	...force letters to be UPPER CASE?		...return a table's intersecting row & column value?		...display the workbook path and name in a cell?
	...limit how many characters can be entered?		...return the second highest or lowest number?		...display the workbook path, name, sheet in a cell?
	...establish the "carriage return" affect in a cell?		...average without zeros?		...name a worksheet tab based on a cell value?
	...fill empty cells in a column with value from above?		...sum, average, or count colored cells?		...change column headers from numbers to letters?
	...stop Excel from converting an entry into a date?		...sum, average, or count to omit Min or Max?		...verify if a certain worksheet exists; create if not?
	...automatically insert colon : when entering a time?		...sum, average, or count across multiple sheets?		...verify if a certain workbook exists?
	...enter a number with a visible leading zero?		...sum, average, or count visible or unfiltered cells?		...verify if a certain workbook is open?
			...sum, average, or count with multiple conditions?		...add more rows or columns to my worksheet?
Data manipulation, extraction, parsing: How to...			...avoid #DIV/0!, #N/A, and other formula errors?		...know how many sheets my workbook can hold?
	...separate alphanumeric numbers and letters?		...use VLOOKUP to return value in column to left?		...sort sheets by tab name in alphanumeric order?
	...separate first and last names into their own cell?				...open a workbook to a certain sheet and cell?
	...extract all to the left of a certain character?	Programming Visual Basic for Applications: How to...			...prevent workbook being renamed or distributed?
	...extract all to the right of a certain character?		...create, install, and run a macro?		...save or export my workbook as an .exe file?
	...move negative sign from back to front of number?		...create, install, and utilize a User Defined Function?		...keep rows or columns visible when scrolling?
	...sort a list by more than three column fields?		...create, install, and utilize Event code?		...make a drop-down list for users to select sheets?
	...convert numeric values to their equivalent words?		...create, install, and utilize Class Module code?		...know when the workbook was created?
	...add or subtract a day, month, or year of a date?		...force another computer to enable my macros?		...know when the workbook was modified or saved?
	...delete rows where empty cells exist in a column?		...stop the screen movements while macros run?		...open all workbooks, modify them, then close all?
	...delete hyperlinks but keep their underlying text?		...close a workbook without the prompt to save it?		...recover or undo a worksheet I wrongly deleted?
			...delete a sheet without the prompt to confirm it?		...make a workbook expire by deleting itself?
Data Validation: How to...			...save as existing name without prompt to confirm?		...convert each worksheet into its own workbook?
	...make the cell's drop-down arrow always visible?		...deal with a Macro warning when no macros exist?
	...type the first letter to select that part of the list?		...create a Message Box timed to click OK by itself?	XL Zone: Assorted questions...
	...adjust the drop-down list's size, width, or font?		...establish a "carriage return" line in my MsgBox?		Why Excel asks to save when nothing changed?
	...have more than 8 visible drop down list items?		...display a progress bar while my macro runs?		Why Excel asks to update links but there are none?
	...have one list dependent on another list?		...copy to or go to the next available row or column?		Why the 'Too many different cell formats' message?
			...locate the first empty cell in a row or column?		How do I make the Reviewing Toolbar go away?
Format: How to...			...know the last row for one or more columns?		*How do I find wildcard characters "?" and "" ?**
	...conditionally format for more than 3 conditions?		...know the last column for one or more rows?		How do I keep a button always visible on the sheet?
	...colorize the active cell, row, or column?		...know the address of the sheet's used range?		How do I autoclose a workbook after inactivity?
	...format a date when concatenating it with text?		...know the address of the sheet's data range?		How can I put a running clock on my spreadsheet?
	...make a font invisible so a cell's value is not seen?		...select a range of unknown size or location?		How do I ask for input of an unambiguous date?
	...make a cell's value flash or blink to get attention?		...fill a formula down to the last used row?		How do I customize the buttons on a Message Box?
	...format a formula's returned value characters?		...find multiple entries of the same value in a range?		How do I show a MsgBox in a corner, not centered?
	...format the sum of times for hours and minutes?		...get the user name of the computer or network?		What can I do to learn more about Excel and VBA?

How to return the last number and its cell address?

Example

Formula last number

Formulas

Based on the list in column A of numeric and text values, the last numeric value, its row, and its address are returned by formulas in cells D2, E2, and F2.

The formula in cell D2 which identifies the last number in column A is
=LOOKUP(9.99999999999999E+307,A:A)

The formula in cell E2 which identifies the row of the last number in column A is
=MATCH(9.99999999999999E+307,A:A)

The formula in cell F2 which identifies the address of the last number in column A is
=ADDRESS(MATCH(9.99999999999999E+307,A:A),1)

Based on the list in column A of numeric and text values, the last text value, its row, and its address are returned by formulas in cells D3, E3, and F3.

The formula in cell D3 which identifies the text value in column A is
=INDEX(A:A,MATCH("*",A:A,-1))

The formula in cell E3 which identifies the row of the last text value in column A is
=MATCH(REPT("z",255),A:A)

The formula in cell F3 which identifies the address of the last text value in column A is
=ADDRESS(MATCH(REPT("z",255),A:A),1,1)

Based on the list in column A of numeric and text values, the last value or either a number or text, its row, and its address are returned by respective formulas in cells D4, E4, and F4.

The formula in cell D4 which identifies the last value of any type in column A is
=INDEX(A:A,COUNTA(A:A))

The formula in cell E4 which identifies the row of the last value of any type in column A is
=MAX((ROW($A1:$A1000)*($A1:$A1000<>"")))
Note, this is an array formula.
Array formulas are entered by simultaneously pressing the Ctrl+Shift+Enter keys.
The curly braces are not entered by the user; Excel places them automatically when the array formula is properly entered.
For this example, we set the range as A1:A1000. When dealing with array formulas, keep the evaluation range as small as possible; that is, don't exceed more cells in the range reference than you really need to.
An alternative formula which is not an array (so it can be entered normally), but which evaluates column A twice to return the correct answer, is
=MAX(MATCH(9.99999999999999E+307,A:A),MATCH(REPT("z",255),A:A))

The formula in cell F4 which identifies the address of the last value of any type in column A is
=ADDRESS(COUNTA($A:$A),COLUMN($A$1),1)

Top of Page

List of Excel "How to" FAQs

Home

About Atlas

Excel Consulting

Excel Training

Excel Testing

Atlas Store

Contact Atlas