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
762 Views

Similar Articles

[PageSpeed] 50

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:

restoring GP 10.0 32-bit into GP 10.0 64-bit
Hello: We are going to install GP 8.0 on a 32-bit server in-house and restore a client's 8.0 data into these SQL databases. Pretty cut and dry. Next, we will upgrade this in-house server from 8.0 to 10.0. Continuing, we will install GP 10.0 on the client's new 64-bit server. Finally, we will restore the upgraded 10.0 databases from our in-house 32-bit server to the client's 64-bit server. Here's the question. Is there anything "wrong", from a technical standpoint, in restoring SQL databases from a 32-bit environment into a 64-bit environment? Thanks! chil...

CRM 3.0
Hi All, I am writing a custom report for CRM 3.0 to basically copy the My Activities view but display the regarding and To contacts and associated phone numbers. The report is basically done except for a few small issues. I would like to set up the Dynamic Drill-Through so when the person clicks on the Activity Subject it will open the associated Activity record. Unfortunately I have been unable to find the information needed to use in the following code to set the Object Type Code (OTC) to the correct activity type: = Parameters!CRM_URL.Value & "?ID={"&Fields!Activityid...

Summing Values using multiple criertia
Does anybody know a formula I could use to sum a range of values based on multiple criertia? Example: Division Type Wage Bulk Driver 200.00 Bulk Admin 400.00 General Admin 500.00 Bulk Driver 100.00 I want to sum the wages for Divison "Bulk" & Type "Driver". How can I do this??? Thanks! Jane =SUMPRODUCT((A2:A4="Bulk")*(B2:B4="Driver")*(C2:C4)) If there are lots of such totals, you may want to consider a pivot table rather than formulas. On Mon, 27 Sep 2004 20:14:27 -0700, "Jane" <anonymous@dis...

how do i change the default value of measure from points to inche.
how do i change the default value of measure from points to inches when setting the width and hight of cells? You don't. Excel uses only points for these measures best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "yoyo4u" <yoyo4u@discussions.microsoft.com> wrote in message news:33420157-6E05-4A55-9003-088D731E495E@microsoft.com... > how do i change the default value of measure from points to inches when > setting the width and hight of cells? yoyo Row heights are measured in points. There are 72 points to an inch. Th...

How to use MSExcel to plot Column A against Col B?
I want to plot col A (x axis) against col B ( y axis). I cant seem to do it. Can anyone here please give me step by step primer. Thanks in advance. All I get is the graph of (1,2,............n) (x axis) against the n values of either col A or B. i.e 2 graphs instead of one. (In the old Lotus this was so simple: select the column for the X axis and then select the col for the Y and press enter, and you'd get the chart) Why is it so diff in Excel? Select the entire range you want to graph such as a2:b44>insert>chart>>>> -- Don Guillett SalesAid Software dguillett1@au...

Last day of posting via Microsoft server
Well, I guess this is the last day of posting via the Microsoft server. http://www.teranews.com/ Your own free account ($3.95 one time setup fee) that allows posting or use public.teranews.com without an account (no posting & speed capped). You can use any standard news client you choose to read and post to any newsgroup. Or Google it: http://groups.google.com/groups/search?hl=en&q=microsoft.public.win98.gen_discussion&qt_s=Search God Bless America, Bill O|||||||O mailto:BillHughes@billhughes.com http://www.billhughes.com/jeep_bookmark.htm "D...

Chart to show Portfolio Value over time?
I'm using Money 2003. I would like to be able to see the $ value I have in my portfolio over time. So that I can weep. Money does have a chart view that allows you to see the PRICE history for a given stock over time - but not the dollar value of your investment in the stock over time. In fact, I can't seem to find a view at all that shows you the net value of your portfolio/individual stocks changing over time. The best I've been able to do is to use the "Net Worth" report and unselect all the other accounts. This has insufficient granularity (months instea...

Printing
Hope you folks can help me out with a strange one. I have several worksheets formatted in exactly the same way as follows: Col A - width 4 Col B - hidden Col C - width 4 Col D - Width 108 Col E - Width 3 Col F - Width 11 Col G - Hidden Col H - Width 11 & Empty My print range should be Cols A:G (I have used page setup to set the scaling to fit 1 page wide by [blank] pages tall, thus each sheet will print as many pages as required depending on number of rows] When I have the print range set to A:G only columns A:E show on the print preview (and also on the actual print out) and when I m...

Amazing Problem in AllocPhysMem wince 6.0
Hi I am writing a Port Driver for x86 platform in WinCE 6.0. I want to allocate virtual and equivalent physical memory in driver and mapped it to USER mode to use application. For that I used AllocPhysMem in driver and passed that address through IOCTL calls but i cant use that virtual and physical address in application side. Because AllocPhysMem returns Error Code as 0x57 (meaning Parameter incorrect). But the same code is working in WinCe 5.0. My code snippet is, VirAddress = (LPVOID)AllocPhysMem(32, PAGE_READWRITE|PAGE_NOCACHE, 0, ...

Reports and making it look prettier: Last Name, First Name Rank
I'm trying to pretty up my report by eliminating the forced space created by having one field of the report for 'LastName', one for 'FirstName', and one for 'Rank.' The Rank isn't too huge of an issue, and if three items in one field gets to be too much, I have no problem leaving that as a side item of sorts. But, I want my report to look a bit better by putting the names together! I want the report to go to my table, pull the LastName from that column, and pair it with the matching FirstName in the column to the right. (Since it's just...

Insert empty numeric value
Dear all, In VB, I have three textbox which are amount1,amount2 and amount3. After user enter the value in the textbox, I will insert the value into Access table. The table have three columns amount1 , amount2 and amount3, and all are nummeric Type. However, if the user do not enter any value in textbox . The insert statement will become as follows: Insert into table1 (amount1,amount2,amount3) values (,,) Then access complain that there is syntax error in insert statement. Does that mean I cannot insert empty value for the numeric value in access.? How to solve this problem. Than...

Subtracting value from main form
I have a borrow module which will alow user to return item separately. So, I have get the structure of returning it separately. In my main form is the borrowing item, with the loaned quantity and the owed quantity (will be calculated). In the subform, there is the returning transaction. User will need to key in the quantity returned and it will be automatically deducted from the quantity owed. But how am I supposed to get the quantity deducted while it 1 is in main form and the other is in subform? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-fo...

GP 6.0 Security
Hi! I am setting User Classes in GP 6.0 and am interested if there's any document showing relations between windows, reports, files. Information in Tools/Resource Decriptions doesn't look usefull. For example if I need to enable Trial Balance Report option for the user, which Windows, Reports and Files have to be included in order for the report to print correctly without 'not privileged' errors. What should be better approach: disable everything and add wanted options or enable everything than disable just screens that I don't want users to see. Latter looks easi...

eService Call with GP 9.0
eService GP 8 release was unchanged for GP 9.0 as per Article ". eService & eReturns NOTE: eService & eReturns for Microsoft Dynamics GP 9.0 utilizes the same download as Microsoft Great Plains 8.0. There are no changes to eService & eReturns requiring an upgrade or installation difference. " I tried to configure eService for GP 9.0 but not able to view the proper page, it seems the COM components “Webservice” not working properly. Anyone come across the same situation? You need to go to Component Services, Computers, My Computers, Com+ Apps and make sure th...

Replace null value with the previous value?
I have a database that was just imported that has approximately 388000 records. The problem is that there is information about a person in multiple different records but the name did not come across with each record. (So I have 10 records with information for a certain name, but the name only appears in field 1 of the first record and not the subsequent 9, etc.) I need to create a query or expression that will fill field 1 with the preceding value if it is null. This way I will have all the information for field 1 in a manner that I can link and combine data. Simply I need to fil...

Averaging cell's...problems with Div/0
Hi guys. First time poster here so be gentle with me. I am looking fo some assistance averaging a range of 1 to 3 numbers. Here is what I have so far. =(D3+F3+H3)/(IF(C3=0,0,1)+IF(E3=0,0,1)+IF(G3=0,0,1)) This works great. What it does is checks to see if there is a value i the cell, then counts it and divides by the right number. I.E if yo only have two values out of 3 filled in it divides the number by tw instead of 3. My problem... if all 3 fields are 0 then I get a divide by 0 error. Any suggestion on how to fix this? I don't want my spreadsheet to loo messy before I start plu...

How do I find a value on a line?
I made a line graph of data to use as a calibration. I know the y value and I want to find the X value. Is there a way that I find find this specifically on the line without using a trendline formula or guessing by looking at the gridlines? The only way to find a specific value is to use the formula. Rgs, Bou If you can accept piece-wise linear interpolation, see Interactive Chart http://www.tushar- mehta.com/excel/software/interactive_chart_display/index.html -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity s...

initial default column width
Is there a way to configure Excel 2000 so that when I create a new Workbook or add a new Worksheet so that all the columns have a particular width instead of the default 64 pixels? TIA Create the workbook exactly the way you want it, then save it as a template with the name "Book.xlt" (no quotes) in you XLStart directory. It'll be then used as the template for new workbooks. Likewise, save a one-sheet workbook as a template, named "Sheet.xlt" for the template for Insert/Worksheet. In article <419E181F.6251D20D@nospam.net>, Bruceh <bruce@nospam.net&...

Windows and Mac have two distinctly different units for column width.
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I am trying to format columns for some data I am entering in a spreadsheet and when I enter &quot;15.00&quot;, which is the required width for these columns given by my professor, I end up with a column 15 inches wide. What I would prefer is for the options to be like the default options in Windows version of Excel. In Windows version of Excel, when you hover over the lines between the columns it gives you two numbers (e.g. 8.43 (64 pixels)). These are the default numbers for column width in Windows Excel....

installing GP 10.0 on workstations only
Hello: I understand that you can install GP 10.0 and forego installation of the application on the server. If you choose to go that route, then how do you path to SQL during the first-time run of GP Utilities when you create the company databases? I mean, if you install GP on the first workstation without installing GP on the server at all, you have to tell GP the path to the database (in the Database Setup window). Can you use UNC pathing in that window or do you have to use mapped drives? Also, what sort of security do you need to create the databases from the workstation? Domai...

Login problem to CRM 3.0
Hi, I installed CRM 3.0 Professional Edition without any error. I can login perfectly from the CRM server either using http://localhost:5555/ or http://appserver:5555/ under the crmadmin user. If I login to an XP workstation with the same user (crmadmin) and specify http://appserver:5555/ in IE6, I got a login window - where I enter the correct DOMAINNAME\crmadmin username and password - and got the following message: "You are not authorized to view this page"..."HTTP Error 401.1 - Unauthorized... I think the prolem is around IIS security, but what shoud be the next step. Th...

How to assign unique number to column duplicates?
Hi All, I need to assign a unique number to a set of duplicates all in one column in Excel 2007. so columnA will has about 9000 numbers, some of them unique, and others are duplicates of 2-4 approx. I used to conditional formatting to show which are duplicates, but need to be able to assign a unique number to each set duplicates, that will be in sequential order... e.g. ColumnA ColumnB(unique ID) 01233 0001 01233 0001 01234 - 01255 0002 01255 0002 etc.... Any ideas please? I don't know how to do programming, just form...

Calculating values for empty cells.
Hello. I have a very simple problem that I cannot find the answer to. I have data in two columns, some of the data in one of the columns is missing and I want to automatically extrapolate what the data should be based on the trend. How can I get Excel to fill in empty values without overwriting the known values. Below is a sample of my data. 1500 1600 1700 1800 4000 1887 5700 1900 5500 1910 7300 1912 8100 1920 8800 1926 10100 1930 11900 1936 12200 1938 -- Ryan Taylor rtaylor@stgeorgeconsulting.com Not sure what yo...

Default value for custom field?
How can I populate a custom field with a default value? Specifically, I have a custom field "DisplayName" associated with the Quote Detail object. I want initially to populate this field with the value of the product name field when a new product is added to a quote. The user can then edit the DisplayName custom field if desired. The DisplayName custom field will be used as the product name on a Crystal Reports quote form. ...

Filter two columns with criterion applying to one or the other?
Hi, I am looking for a solution to the following filtering problem: I have two adjacent columns, so using a filter for both of them is no problem. But what I want to do and don't know how to do is this: I want to filter for values greater than x (a certain number, in my case 5000) in any of the two columns. I can filter both columns for x greater than 5000 but that filters out more than I want because there may be some cells with a value greater than 5000 in only one of the two columns. Is there a solution to this problem (using Excel alone or an add-on)? Peter Hi Peter you can use th...