#### 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.

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

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...