Count last nonzero number in a column

Hi

I have columns of numbers, A:Z.  Each column has either numbers, zero's, or 
text.
I need to find the row number that has the "Last" nonzero number.

So if A1="car" , A2=0, A3=6, A4="Boat" 

I would need a value of 3, the last occurence of a nonzero number (6).

Thanks for your help
0
Jeff1 (635)
10/5/2004 1:35:07 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
840 Views

Similar Articles

[PageSpeed] 40

Hi
try the following formula:
=LOOKUP(2,1/((A1:A100<>0)*(ISNUMBER(A1:A100))),ROW(A1:A100))

--
Regards
Frank Kabel
Frankfurt, Germany

"Jeff" <Jeff@discussions.microsoft.com> schrieb im Newsbeitrag
news:FEA7DC58-6459-4F55-9DE4-984740EFC610@microsoft.com...
> Hi
>
> I have columns of numbers, A:Z.  Each column has either numbers,
zero's, or
> text.
> I need to find the row number that has the "Last" nonzero number.
>
> So if A1="car" , A2=0, A3=6, A4="Boat"
>
> I would need a value of 3, the last occurence of a nonzero number
(6).
>
> Thanks for your help

0
frank.kabel (11126)
10/5/2004 1:38:29 PM
Reply:

Similar Artilces:

how to I block text not to break across columns or pages ?
I want to keep certain text together and not have it split apart over columns or on a new page: like several lines comprising an address in an address book -- I want to keep it all together. Either apply a style with the Keep With Next paragraph format option to all but the last of the paragraphs you wish to keep together or Use Shift+Enter to make a line break within the same paragraph -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MV...

update the same cell into another column forming a list on opening
hi there, i have made a spreadsheet to enter data and then view the resulting statistics that are generated, i have one cell that changes in value after entering the data, and i would like to keep a record of what values this cell has been, i was after a formula that would update that single cell's value into a column forming a list each time the spreadsheet was opened or the value in the cell changed, like a field perhaps, this would fill up the column with a list of results from which to generate more stats and a graph etc. any help would be great, thank you -- thank...

How do I add column A data labels to a scatter of columns B and C?
I have made a scatter plot using columns B and C as my x and y coordinates. I would like to use the data in column A to label my points. I already have the XY chart labeler 97 installed and selected, but I don't know how to get the data labels to show up on my graph. Thank you! Hi Christina - There are a couple of well written Excel add-ins you can download to do this: Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tools, http://j-walk.com Both are free downloads, they install easily, and they integrate into Excel's interface very nicely. They l...

Report showing ID numbers only
I have tables set up, linked properly, forms, etc. Since I did forms and subforms, I had to create separate tables and link them, so I used a primary key ID in the tables. So far my database shows it is working - when information is entered into the form, it shows up in the correct tables and all that, but since I used all these primary keys, the tables just have a bunch of the numbers that pertain to what was input rather than actual words. I created a query with the fields that I want shown in the report, and I created the report and when I run it it shows up the correct fields I want...

Excel 2003 random number generator
Hi, I'm using Excel 2003 to do some actuarial work. I notice that if a very large number of random number is generated(say 10000), and the spreadsheet is run by Macro for a large number of times(say 1000), sometimes, Excel 2003 will generate some negative random numbers, which is nonsense. Some of my friends told me earlier versions of Excel didn't have that problem. I'm sort of wondering what is going on with Excel 2003 random number generator and if Microsoft is going to develop a update to fix this problem. ---------------- This post is a suggestion for Microsoft, and Mic...

Fixed Number of Columns
After manipulating data in Excel I would like to copy and paste the data in a text editor. The data should be in fixed format, for example integer numbers should end in columns with multiples of 5. The text is then used as input to an old fashioned program which requires that the data be aligned I changed the font in Excel to Courrier New and then selected a column width of 5. When I copy the data to a text editor it copies some tab marks and the columns do not align. How can I make things align in the text editor? Vino, You can use a macro: copy the code below, then paste into a module...

Locking a column. Excel, 2002.
I don't know if locking is the correct phraseology and perhaps that is why I can't find an answer. In column A are the numbers, 1, 2, 3, and so on and I want these numbers to remain stationary (they are the rank ordering) when I do a sort on another column (say H). -- Regards Ron Badour Select the range that you want to sort, but avoid column A. Then sort your data. If you let excel guess at the range, it'll think that you may want all the contiguous columns sorted. Personally, I would sort that column, too. But I'd fill it with a formula like: =row()-1 (headers...

Count unique names in a list
hi How do I count a list of names, some names appear more than once. I only want to count unique names. I use excel 2007 Thanks Cassie One way... =SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20,A2:A20&"")) -- Biff Microsoft Excel MVP "Cassie" <Cassie@discussions.microsoft.com> wrote in message news:B610A13C-FAF8-46EF-A63C-AAE88EEFB89F@microsoft.com... > hi > How do I count a list of names, some names appear more than once. I only > want to count unique names. > I use excel 2007 > Thanks > Cassie For ...

format numbers in hexadecimal
I'd like to display numbers in hexadecimal and still be able t calculate with them. DEC2HEX() and HEX2DEC() are available as functions (I think they're i the Analysis ToolPak) but I'd rather avoid adding these to ever calculation. That is, for example, I don't want to have to change every formula "=A1+A2" to "=dec2hex(hex2dec(A1)+hex2dex(A2))". Any idea how to add "Hex" as another numeric format choice, so tha numbers are displayed directly in hex -- Message posted from http://www.ExcelForum.com stan_shepherd wrote... >I'd like to ...

how to use TrackPopupMenu last parameter
Hi, I need to make a menu that does not disappear. (Odd, yes. I need to let users edit menu resources at runtime and would like to display the menus as how they would actually look.) So I'm looking at CMenu's TrackPopupMenu function, whose last parameter is LPCRECT lpRect. I figure if I set this rectangle to be (0, 0, screen width, screen height), then even if the user clicks elsewhere on the screen, the menu won't disappear, right? I tried the following code and the menu still disappears when I randomly click elsewhere in the application. What am I doing wrong? void CMenuVi...

how to find numbers that give another number
hi all. I need to make a macro which look up a column of an excel paper and find the numbers that once summed give the number in cell b5 for example!!they could be 2 or 3 ..or 4..and so on!! You would need the solver http://tinyurl.com/4doog example -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Rossella" <rossy77@gmail.com> wrote in message news:1145464775.044405.243700@v46g2000cwv.googlegroups.com... > hi all. > I need to make a macro which look up a column of an excel paper and > find the numbers that once summed give the number in ce...

How does one print the columns from a Visio ERD diagram?
I see them but can't print them. ...

Excel 2000... Missing row and column designators.
Ho do you mean 'Tools - Options - View' and check 'Row/Column headings' -- Regards Frank Kabel Frankfurt, Germany "Clarence" <carowe@cnyconnect.net> schrieb im Newsbeitrag news:2ee7401c46c21$a45aef40$a501280a@phx.gbl... > ...

Picking Random Numbers
I have been tasked with coming up with a form that will automatically assign a random number to an individual with the click of a button. The number is 7 digits in length. Each element of the number can range from 1 to 9. Something simular to a lottery number picker. Does anyone have any sample code that I can refer to? Thanks in advance! Dwight Function GenerateRandomNumber() As String Dim lngLoop As Long Dim strResult As String Randomize For lngLoop = 1 To 7 strResult = strResult & Format((Int(9 * Rnd) + 1), "0") Next lngLoop GenerateRandomNumber = strResu...

can not accses my email i get Error Number: 0x800CCC32
Unable to poll for new messages on your HTTP server. Account: 'Hotmail', Server: 'http://services.msn.com/svcs /hotmail/httpmail.asp', Protocol: HTTPMail, Server Response: 'Authorization Required', Port: 0, Secure(SSL): No, Error Number: 0x800CCC32 abe <anonymous@discussions.microsoft.com> wrote: > Unable to poll for new messages on your HTTP server. > Account: 'Hotmail', Server: 'http://services.msn.com/svcs > /hotmail/httpmail.asp', Protocol: HTTPMail, Server > Response: 'Authorization Required', Port: 0, Secure(SSL): ...

Why are my columns and rows reversed when I open Excel
When I open my Excel spreadsheet the columns, rows and scroll bars are reversed?? What's happened? Tools|options|International Tab Uncheck that "view current sheet right-to-left" Deb Web wrote: > > When I open my Excel spreadsheet the columns, rows and scroll bars are > reversed?? What's happened? -- Dave Peterson ...

Merging queries by column in access
Hi, I have a series of queries which get data aggregated by year, e.g. select year(date), min(var1), avg(var1), max(var1) from table where (ridiculously complicated set of conditions) group by year(date) select year(date), min(var2), avg(var2), max(var2) from table where (entirely different ridiculously complicated set of conditions) group by year(date) etc etc There are six queries like this so what I want is to amalgamate these columns together into one results table (and also into one query so users can just run one stored query rather than six!) as follows: year, m...

Exporting As Text with X Number of Spaces Between Data
Is there a way to export data in a MS Excel spreadsheet as text and define the amount of spaces between the data being exported? The number of spaces need to vary. This concept is the opposite of importing text as "Fixed Width" and creating column break lines. Instead, I'd like to export columns into a text file and specify the amount of spaces between the text that is being exported from each column. The amount spaces in the text file needs to be different between each set of column data. Thanks! One way: Say you have 5 columns, and you want 2 spaces between A &...

A If then running count
I am trying and just cannot get it. I am trying to get a formula that does this: If have cell n3 at numbers up to 9 then cell v3 I want to read "1" If have cell n3 at numbers up to 14.9 then cell v3 I want to read "2" If have cell n3 at numbers up to 19.9 then cell v3 I want to read "3" If have cell n3 at numbers up to 24.9 then cell v3 I want to read "4" and so on. My attempt is to make the numbers increase by 1 when counting by 5. can anyone help me on this. How about this? =IF(A1<=9,1,ROUNDDOWN(A1/5,0)) ************ Anne Troy www.OfficeArtic...

Is there any way to use the MATCH function with more than 1 column
For example, =MATCH(215, Y3:Y51, 0) will search from Y3 to Y51 for the value 215, but my data set spans more than one column. My data set ranges from A3 to Z51, and the function =MATCH(215, A3:Z51, 0) returns an #N/A error. What can I do? Do you want both the row and column ? Two separate values? -- Gary's Student "Mr. Snrub" wrote: > For example, =MATCH(215, Y3:Y51, 0) will search from Y3 to Y51 for the value > 215, but my data set spans more than one column. > > My data set ranges from A3 to Z51, and the function =MATCH(215, A3:Z51, 0) > returns an #...

Columns in Money 2004
Is there any way to change the default columns in the register view ? I really dont want the flagged column, and would like to include a column for categories. Is it possible to do this ? In microsoft.public.money, VerticalCobra wrote: >Is there any way to change the default columns in the register view ? I >really dont want the flagged column, and would like to include a column for >categories. Is it possible to do this ? No. Try Ctrl+T to toggle to multi-line display and back. ...

Getting info from the last used cell
Currently I am adding many sheets together. I am using "Start Sheet" and "End Sheet" and everything in between those two sheets is adding for a total. I would (in a few cells) to just get the data from the last cell used. Example: The sheets I am adding are dates and if sheet 01-19-09 (dd-mm-yy) has the last entry in cell c18 I would like that number. However there may be blank cell before that sheet. and after that sheet there will be info in other cells. I would like that info to go into c18 of the sheet I use and named "Totals". -- Ed Davis ...

Retrieving the Top 10 of the Average of Two columns, but displaying a third
I'm new to Excel programing, so please bear with me. I have an excel spreadsheet with 3 columns. Column1 Column2 Column3 -------------------------------------- John Doe 5 3 Jane Smith 2 9 Bill Smith 4 2 What I need to do is take the average of Column 2 and 3, find the highest average of the entire list and display Column 1 as the final result. In other words: I need to create a Top 5 List of the people with the highest grade average. I don't care much for displaying the grade, I just need the top 5...

Add number of days due field on aged trial balance reports (PM/RM)
I would like to add 'number of days past due' field on the aged trial balance reports and sort the reports by this field. I dont know how to proceed. Can someone guide me? ...

how do i "de-dupe?" rows by a column value?
I have a complex operation to perform. My spreadsheet has rows that indicate items (control number in column A) linked to titles (control number in column B). A title can have multiple items in the spredsheet. I want to number the titles and assign random numbers to them so that I can get two random samples. I also want to have my random numbering retained in the spreadsheet as a whole, so I can see all the items assigned to my samples. How can I "dedupe" column B, assign a random number to each title control, then transfer that data to my "undeduped" spreadshe...