Last Value in a Column when Value <> 0

Hi, can someone provide me a formula that populates a cell with the
last value in a column that does not = 0?
Below is an example
3
6
7
0
0
0

The goal is to populate a cell with the value of 7. I am currently
using the below formula that populates the last value of a column:
=INDEX('Retirement Total'!B:B,MATCH(9.99999999999999E+307,'Retirement
Total'!B:B))

I do not however know how to change this to not populate the last
value when it is zero. Your help is appreciated.
0
11/3/2009 3:55:59 AM
excel 39879 articles. 2 followers. Follow

1 Replies
788 Views

Similar Articles

[PageSpeed] 1

Assuming you want the last *numeric* value that <>0.

Try something like this:

=LOOKUP(1E100,1/'Retirement Total'!B1:B20,'Retirement Total'!B1:B20)

Note: unless you're using Excel 2007 (or greater) you can't reference the 
entire column B:B. You'd have to specify a range.

-- 
Biff
Microsoft Excel MVP


"SanCarlosCyclist" <sancarloscyclist@gmail.com> wrote in message 
news:169bb128-9fc7-4ce1-9a92-bd3d9d1b5ffd@a39g2000pre.googlegroups.com...
> Hi, can someone provide me a formula that populates a cell with the
> last value in a column that does not = 0?
> Below is an example
> 3
> 6
> 7
> 0
> 0
> 0
>
> The goal is to populate a cell with the value of 7. I am currently
> using the below formula that populates the last value of a column:
> =INDEX('Retirement Total'!B:B,MATCH(9.99999999999999E+307,'Retirement
> Total'!B:B))
>
> I do not however know how to change this to not populate the last
> value when it is zero. Your help is appreciated. 


0
biffinpitt (3172)
11/3/2009 4:15:30 AM
Reply:

Similar Artilces:

show another column when hovering on a point in a chart
Is it possible to show another column when you hover on a point? I have 3 columns, A, B, and C for a scatter plot I have B and C as x and y coordinates of a point, and I'd like to have Excel show A when I hover on the point. thanks, Wei ...

Can the column index in a cell address be made variable?
Hi, To refer to a cell with a variable row number, we can just code it as, for example, Dim i As Interger i=234 Range("A" & i).Select To refer to a cell with a variable column index, it seems not that easy because the column index must be explicitly specified in a cell address. So, if I want to go to the j th column on the 2nd row or j columns to the right of cell AA3, is there a quick and easy way to do it? Thank you in advance. David You may be able to use R1C1 terminology, but you'll need to get someone else to help from here. "cyberdude" <honc...

Splitting First and Last Names
I have a spreadsheet where the Name feild contains both first and last names. I want to split this into two fields. How can I do this? Hi use 'Data - Text to<columns' for this -- Regards Frank Kabel Frankfurt, Germany Jamie wrote: > I have a spreadsheet where the Name feild contains both first and last > names. I want to split this into two fields. How can I do this? "Jamie" <pastorjman310@hotmail.com> wrote >I have a spreadsheet where the Name feild contains both >first and last > names. I want to split this into two fields. How can I do &g...

Not allow entering repeated references in a column
Frank, If you don�t mind ... I will send it :) . As I don�t know your e-mail, I will give mine, which is ssouritinha@sapo.pt Just send your e-mail to my e-mail box and I will send the file. Thanks, Ritinh -- ritinh ----------------------------------------------------------------------- ritinha's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1566 View this thread: http://www.excelforum.com/showthread.php?threadid=27195 ...

Change <value> in outlook
We Have Exchange 2003 sp2 and outlook 2003 sp2. I have renamed 2 AD accounts. Changed all display names, accounts name etc with the 2 accounts. When users open outlook and send to renamed account they see account name <oldaccountname> in the autocomplete list. I have cleared nk2 files in profile , but it still shows newaccount <oldaccountname> in drop down box. Is there a way to edit the <oldaccountnam> value of the renamed account? I tried the same thing with a freshly loaded PC. with the same result. Thanks You can delete the nickname files. -- Ed Crowley MVP - E...

chart label reference based on the column number
In a worksheet with an embedded chart, I have a cell, S4, where I enter the number of the column I want to chart (these numbers are listed as labels in cells A2:R2). I can't figure out how to translate the number in S4 into the corresponding column letter. For example, if S4 contains "3", the chart title should be =$C$2. I think working with absolute references, R1C1, might be easier here, but somehow what I've tried, didn't work (the entire spreadsheet is based on relative addresses (A1). z.entropic in a cell (Z100) put =index(A2:G2,1,S4,1) Change G2 to whatev...

Count unique values
Hi, I've some problems to count unique items (Invoice #) in a pivot table. There is the default solution "Add-a-new-calculated-column" as mentioned on http://contextures.com/xlPivot07.html#Unique, but in my case, it doesn't works. My problem is, that these values aren't in a Excel worksheet; it's a external data source - a SQL-Select via DAO/ADO. Because of that, I don't have the possibility to add a new column. Another reason is, that the pivot table should always be dynamic: Group over this field, group over another field - and always show the number of unique...

Sorting Alphanumeric values in a text field
I'm using Access 2003 for a database for my company. I have a field in a table that has both text and numbers. They are part numbers, for example 21BC124. I kept the field as text because of the text with in the numbers and didn't figure that a numeric field would alow the text. In my part numbers table it sorts correctly (first by number then by letter then by number again), but in my reports and queries there are a few number that sort in the wrong place. Like this... 20D10-3 21BC123 21BC128 22D10 25TD47 21FA101 21FA200 25FA203 38FA601 21FP604 38WS100 I can't quite f...

Why should I upgrade to 8.0?
If you were currently running MBS 7.5 at the moment, are there any compelling reasons to move up to 8.0? Or would you be better off waiting it out for their 9.0 release? If you haven't seen reason enough, you might as well wait. When you do decide to upgrade, you will still have to go through 8.0 as there is noupgrade path from 7.5 to 9. Your VAR will do both upgrades one after the other so there should be no downtime. HS "ElmerT" <elmer.tagarino@gisimplement.com> wrote in message news:%23gx97N6zFHA.2652@TK2MSFTNGP14.phx.gbl... > If you were currently running...

Top values
Dear friends, need your help again please. I have a table: tbl_Plots (PlotID is the primar key - number byte) and tbl_Data (ID is the primary key - autonumber, PlotID related to tblePlots, height - number byte and diameter - number integer). I need to select the 20 bulkiest trees of each plot, i.e. having the biggest diameter. Also, perhaps in a plot less than 20 trees will be present so I will need all of them. Any suggestions? Thanking you in advance, GeorgeCY hi Geroge, George wrote: > I have a table: tbl_Plots (PlotID is the primar key - number byte) and > tbl_Data (ID is...

Using Sumproduct when some of the values are null
I am having a problem using Sumproduct when some of the cells have null values. I will try to describe the problem. Please let me know if you need additional information. Do any of you have any ideas of how I can fix that sumproduct to get it to work? A B C Row Trend Claims 5 Emergency $0 6 Emergency $0 7 Emergency $0 8 Emergency 81.68% $24,444 9 Emergency 35.00% $164,758 10 Emergency 35.00% ...

4.0 Acceleration Software?
Have heard tell of a software "maybe called accelerate" that can be installed on the CRM server, which will increase speed and performance. My clients are complaining of slowness since crm has been installed. They all use Outlook client for desktop, and say there is a definite latency. I've been searching the web, but coming up empty on a software for this. Is it an urban myth or a secret that needs to be be shared? ...

Great Plains integration to CRM 4.0
does anyone know if there is integration of great plains with crm 4.0? we have crm 3.0 integrated to great plains 8.0 and want to upgrade both. Hi Shawn, There is no Microsoft connector yet for CRM 4 and GP. There is one for CRM 3 and GP. Other options are to use • some third party tools like Scribe • or use Microsoft BizTalk • or do custom Programming. -- uMar Khan :: MS CRM MVP Microsoft CRM Consultant Email :: imumar at gmail dot com Blog :: http://umarkhan.wordpress.com MVP :: https://mvp.support.microsoft.com/default.aspx/profile/umar.khan "Shawn" wrote: > does an...

Comparing first and last names in two lists
I have two data sources that each contain about 8000 names in seperate fields for first and last name. I can put them on seperate worksheets or append one to the other. I need to make them match and find out where they don't. Messed with consolidate but not sure if that will work for more than one column at a time. FYI data set one is from a school transportation database and data set two is from the main student database from the same school district. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages dir...

Bar charts with negative values and label position
In a bar chart containing both positive and negative values, the X-axis labels (indicated by the Category X series) display over the negative bars of the chart. Is there a way of moving the negative value labels to the right of the axis? I have seen this in both Excel 97 and Excel 2000. To reproduce, 1. Create a spreadsheet with the following in cells A1:B5: abc 100 def -400 ghi 200 jkl -150 mno -200 2. Select A1:B5. 3. Press the chart wizard button and choose a bar chart. 4. Click the FINISH button. I have seen a similar post for this issue for Works for Windows (Article ID : 113...

Passing a variable as a parameter value instead of a literal string to a child report?
In the Navigation section (Jump to URL), I have the following: ="javascript:void(window.open('http://localhost/reportserver?/Reports +Folder/My +Report&rs:Command=Render&StartDate=01/01/2009&FinishDate=12/31/2010'))" This works as designed - a new window pops up with the "My Report" report and the StartDate and FinishDate of 01/01/2009 and 12/31/2010 are passed respectively. But what is the syntax for changing the "01/01/2009" and "12/31/2010" to variables that point to the StartDate and FinishDate parameters of the PAR...

Import Templates for Dynamics CRM 3.0
Hi all I am trying to use the standard import file into Leads, and it is coming up with data type not valid, length not valid however I am 100% sure it is correct. Have any of you out there got formatted xls files that have the correct data types. field lengths that we could possibly use as a template to see if it is actually my error? Regards Wayne Lockey ...

Getting Cell Value from the Concatenate formula
I built a concatenate formula that returns the following result: =Jul! $D27 I am looking for the cell contents of Sheet: July Column D Row 27. I tried to use offset, but I am stumped. Can I add something to the front of the concatenate to not only build the reference to the cell, but also return the value instead of the =Jul!$D27 ? Thanks John =indirect(yourformulahere) Don't include the equal sign in your formula. And match the name correctly (Jul or July???). Depending on the name of the worksheet, you may need to have a string that looks like: 'Sheet 99'!d27 =indirec...

CRM 3.0 Email Attachments
I understand the attachments are stored in the DB iteself. I am able to find out which table and fields. I see attachment NAME, MIME TYPE, MIMEID but unable to locate which column in the MSCRM.ActivityMimeAttachment table the content is stored ? Please advice. I am trying to download the attachment from the CRM Database into a folder as a file for a given activityid. ...

Column comparing
I am trying to compare 2 columns of numbers so that I can identify and delete numbers no longer required. Can anyone help me find a formula for this please? Many thanks DT Check your earlier post. Dave T wrote: > > I am trying to compare 2 columns of numbers so that I can identify and > delete numbers no longer required. Can anyone help me find a formula for > this please? > > Many thanks > DT -- Dave Peterson ...

Hyper link one column to another
I would like to have my columns hyper link one another. For example: Click on B2 would take you to N2, and vice-versa. Click on C2 would take you to O2... and so on through column j linked to V. Is this possible without having to make the link for each cell? This is a timesheet template and the columns b though J are the hours and N through V are the text comments for those hours. When I copy the template to a new sheet, there will be no data in any column. I would like to be able to enter an amount or formula (=end-start) for time spent and then be able to click on that cell and hyperlin...

Prevent Hidden Column data from being copied/pasted?
A student came up with a question that I haven't been able to figure out yet in a recent Excel class. They are hiding a column and protecting the worksheet in the correct manner. They want to allow some users to access and enter information in some cells. They do not want the users to be able to copy and paste the information from the hidden column. The question is how can this be prevented? For example, Column B is hidden. When they copy a range such as A1:C10 and paste it to another worksheet, they are getting the "hidden" data in Column B in B1:B10. Any suggestions wou...

CRM 3.0 Setup err databases already exist on the specified SQL Ser
ReportServer ReportServerTempDB Question : How to clear this problem? The On-Line Help did not applied as SQL 2005 has been installed on SBS2003 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Microsoft SQL Server Reporting Services Cause There are two causes for this error: Microsoft SQL Server Reporting Services is already installed on the specified server. Setup has found a version of Microsoft SQL Server that is not supported by Microsoft SQL Server Reporting Services. Solution One of the following versions of Microsoft SQL Server are required for Microsoft SQL Server Repor...

Deleting Unique Values
How do you delete unique values in a column? I need to filter 7500+ rows to only display duplicate values. The VBA code below will delete the entire row when the value in col. "A" will be uniqe. Consider to make a copy of your entire sheet in order to test the code and see if this is what you need. ------------------------------- Sub Delete_Uniques() LR = Cells(Rows.Count, 1).End(xlUp).Row For R = LR To 1 Step -1 If Application.CountIf(Range("A:A"), Cells(R, 1)) = 1 Then Cells(R, 1).EntireRow.Delete End If N...

combining columns all the way down
I am trying to combine two columns of information in excel but th concatenate function doesnt seem to work for it. It may just be m ineptitude in excel but I just cant seem to figure this out. I have 3 colums Column A__________Column B___________Column C tree_______________ .jpg dog________________.gif House______________.png But I cant seem to be able to make it so that column C has tree.jpg an dog.gif and house.png Is concatenate the wrong thing to use here? or am I making my formula incorrectly -- sparkrom ----------------------------------------------------------------------- sparkro...