Looking Up the First Nonzero Value in a Column

Hello everyone,

I am looking for a function that can return the first nonzero value in
a column.  I have a table that has months across the top and down the
leftmost column.  The data in the table are balances as of each month,
essentially.

It looks like this:

   J  F  M  A  M
J  1
F  2  1  
M  3  2  1
A  4  3  2  1
M  5  4  3  2  1

Is there a way to say, "Look in the column labeled "F" and return the
first nonzero number"?  Then the cell below it will need to be the
number directly below that first number, and so on.  I'd appreciate
any help.  Thanks!

Sincerely,
Tom
0
TKrepitch (51)
10/23/2003 11:58:09 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
523 Views

Similar Articles

[PageSpeed] 41

Maybe something like this

=INDEX($F$2:$F$200,MATCH(TRUE,$F$2:$F$200<>0,0)+ROW(1:1)-1)

copied down, if no negative numbers are possible

=INDEX($F$2:$F$200,MATCH(TRUE,$F$2:$F$200>0,0)+ROW(1:1)-1)

has to be entered with ctrl + shift & enter



-- 

Regards,

Peo Sjoblom

"TKrepitch" <TKrepitch@aol.com> wrote in message
news:21ad25e7.0310231558.6661af34@posting.google.com...
> Hello everyone,
>
> I am looking for a function that can return the first nonzero value in
> a column.  I have a table that has months across the top and down the
> leftmost column.  The data in the table are balances as of each month,
> essentially.
>
> It looks like this:
>
>    J  F  M  A  M
> J  1
> F  2  1
> M  3  2  1
> A  4  3  2  1
> M  5  4  3  2  1
>
> Is there a way to say, "Look in the column labeled "F" and return the
> first nonzero number"?  Then the cell below it will need to be the
> number directly below that first number, and so on.  I'd appreciate
> any help.  Thanks!
>
> Sincerely,
> Tom


0
terre081 (3244)
10/24/2003 12:24:03 AM
Peo,

Thanks for your help.  It worked beautifully!

Sincerely,
Tom
0
TKrepitch (51)
10/30/2003 11:18:24 PM
TKrepitch@aol.com (TKrepitch) wrote in message news:<21ad25e7.0310301518.499bd54d@posting.google.com>...

Could you please help me adjust this formula to do the same thing, but
with rows instead of columns?  I'm pretty close, but still missing
something.

I want to find the first nonzero value in a row of numbers using a
similar equation, then be able to copy it across and down.  Thanks!

Sincerely,
Tom
0
TKrepitch (51)
11/20/2003 11:49:55 PM
Reply:

Similar Artilces:

Hiding zero values in report
Hi, I got some problem, I check a lot of past thread and can find similar question but not exactly the problem that I face. Anybody can tell me how to hide zero values in report. My report comes from Queries. Qty On Hand: [Qty Purchased]-[Q'ty Sold], I put criteria in queries >0 but, it did not work. Message show Qty Purchase? and also Qty Sold? How? Please help? Thks in advance... -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200911/1 Please post the SQL. Open the query in design view, then click Vie...

Column Sort
Hello I have a table of data. I would like to sort a column using a macro however the column is determined by the cell selected. Is this possible. Shazz Shazz If the table is contiguous from cell A1 the code below will work Sub SortBySelected() Range("A1").CurrentRegion.Sort Key1:=Range(ActiveCell.Address), _ Order1:=xlAscending, Header:=xlGuess End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Shaz" <shaz6000@hotmail.com> wrote in message news:8ab3485f.0409201434.25a27fdb@posting.google.com... &...

How to determine the numeric value of a date field?
I'm using Access 2000 and have a db with some queries set up by someone else. The query is supposed to list those who are of a certain age: For those who have a dob that makes them 16 years old plus the query is: <(Now()-5844) For those who have a dob in the age range of 16 years old: Between (Now()-5475) And (Now()-5843) How do I work backwards to determine what the 5844 and the 5843 are supposed to be so I can double-check the results? As it stands now, I have a known 17 year old not showing up on the 16+ list. Thanks for any help you can provide! LH One way to calculate ag...

I have a query with 10,000 records but I only want to see the first 1,000 records...
I have a query with 10,000 records but I only want to see the first 1,000 records How can I get an Access Querie to only return a specified number of records? Thanks Kelvin Add TOP 20 into the SQL statement like this -- SELECT TOP 20 -- KARL DEWEY Build a little - Test a little "Kelvin Beaton" wrote: > I have a query with 10,000 records but I only want to see the first 1,000 > records > > How can I get an Access Querie to only return a specified number of records? > > Thanks > > Kelvin > > > Kelvin Define "first"! S...

All my icons look the same (except IE8 and the recycle bin)
i cant open any windows...when i click on them the open with window pops up and wants me to pick wat i want to open it with...i can only get on the internet in safe mode with networking. and when i try to open...for example paint it says invalid bitmap, or its format is not currently supported and other windows wont even open. got any ideas on how i can fix this?? On Sun, 30 May 2010 06:54:01 -0700, xxkoreanxx wrote in message <news:999F2CE9-8E21-47DC-8BA4-BAC1F1329F6E@microsoft.com>: > i cant open any windows...when i click on them the open with window pops up > a...

Changing column names
Can the columns be named ( changing the A B C etcto the months of the year? Cathy No. The column headings are either A,B,C or 1,2,3. You can hide them though, and use Row 1 as your column header row. Then you use whatever you want for the column headers. HTH Otto "Cathy S" <Cathy S@discussions.microsoft.com> wrote in message news:C6DB1DB3-7ED9-4AAD-8395-E9754DCEBA2C@microsoft.com... > Can the columns be named ( changing the A B C etcto the months of the > year? ...

Looking for feedback
Hey everyone just started my own business doing consulting / training for Microsoft Outlook please check out my site www.outlookchallenged.com and provide any suggestions / feedback please. Any advice is good advice at this point. Thanks -- www.outlookchallenged.com Br Sense <outlookhelp@ftard.com> wrote: > Hey everyone just started my own business doing consulting / training > for Microsoft Outlook please check out my site > www.outlookchallenged.com and provide any suggestions / feedback > please. Any advice is good advice at this point. The best advice I have to offer ...

Subform comparison of 2 tables excluding non zero value
I am creating a subform (at least that is what I am assuming would be best choice)(Access 2003/NT) in which the user will type in a Melt# (contained in the tblChemicalResults). I need to create a side by side comparison of the chemical requirements from the tblChemicalRequirements to the actual results contained in the tblChemicalResults, but I need it to exclude any non zero values from both tables. Each table contains all of the possible 14 different metal elements that could possibly go into making a particular part. Obviously, not all 14 go into every part, so how do I display only ...

looping through columns and rows
Hello, Can someone show me the code which would allow me to search through a number of rows and columns, to identify a criteria. Each time the criteria is found, 1 is added to a variable. When the variable reaches a set number, the search moves to a different column. This would allow me to allocate people to jobs according to their first, second, third choice etc, without resourcing too many people to a job. can you help? I have very little experience, but am usually able to see what is happening in the code and expend on it to take care of more elements. kind regards, Matt Check one...

Looking for CRM Solution
Does anyone use, or has anyone encountered, a CRM based solution used for residentail, multifamily property management? On Apr 28, 12:55=A0pm, MrB <M...@discussions.microsoft.com> wrote: > Does anyone use, or has anyone encountered, a CRM based solution used for > residentail, multifamily property management? The beauty of Microsoft CRM is that it can be customized for any business in any industry. It's ability to be customized using front- end tools (not within code) is , as far as I know, unparalleled by any other software package. You have the ability to create new entiti...

move rows from column to column
I have Column b with Name in one row and number in 2nd row want to move all numbers to Column a and leave name in Column b without having to move each one seperate. 1500 records. Use a help column, in the first adjacent cell type =ISNUMBER(A2) where A2 is the first cell with name copy down 1500 rows by either dragging the formula or double click the lower right corner of the help cell (as long as it is in an adjacent column) Then apply data>filter>autofilter and filter on FALSE in the help columns, select all visible cells and copy and paste somewhere else, then select TRUE from ...

=COUNTA() with multiple look-up
Hi All, I would like to count the number of items that have a value "AAA" in column A and an amount >0 in column B. As I am not quite sure how to incorporate two look-ups in a counta formula I was wondering if anyone can give me the formula? Many thanks! Rgds, Robert Try something like this: =SUMPRODUCT((A1:A100="AAA")*(B1:B100>0)) or....if there may be some text cells in B1:B100 interspersed with the numbers =SUMPRODUCT((A1:A100="AAA")*ISNUMBER(B1:B100)*(B1:B100>0)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP &...

Macintosh User Get First PC with Outlook
Good afternoon ... this is my first ever newsgroup contact - so be patient. Sorry to bother you but we're looking for some help ... We're PC beginners after nearly 20 years with Macintosh ... when we turned on our Compaq laptop with XP it connected into the network (of 6 macs) and begin searching the net just great with Explorer BUT when we tried to set up our e-mail on Outlook it began struggling hard ... it took forever to open up the application after we restarted ... I undid everything to get us back to square one. What can be done? I'm hoping for Outlook to really hel...

can I edit a picture to look faded?
I want to edit my picture to be faded so I can use it as background with text over it. "deborahm" <deborahm@discussions.microsoft.com> wrote in message news:4D93DD63-8553-447D-A0AC-44EE0E1BD9B3@microsoft.com... > I want to edit my picture to be faded so I can use it as background with > text > over it. In Publisher you can do it one of two ways. For both ways start by inserting the picture. Method 1, right click on picture and select Format Picture, click on picture tab, then click on the dropdown for Color under Image Control and select wash...

Help: how to "merge" rows while summing some values
Hi, sorry for the title, but I have no simpler way to put it: here is the issue: I have a worksheet where different orders from the different customers are shown. I would like to get a resulting sheet where I have only one row for each customer, where the total $ amount is shown for each customer. Column A and B identify the customer (name and location), column C and D are the $ amount and number of items. So I have sorted the sheet using A and B: now I would like Excel to uniquify the rows based on column A and B, while showing the total per customer on column C and D Any help is greatly...

Double value subtraction problem....
Hi When I tried to subtract two double values its giving unexpected results!!!! Eg:- 11.846000000000 - 11.846000000000 Its giving some values like -1.02383489238E12 !!!! instead of ZERO !!!! In an MSDN article (Q59407) it says that " subtracting double values greater than or equal to 1.0E+025 may return inaccurate results". But didn't say anything on how to rectify it!!! How to solve this prob? Please help..... rgds, RENAK RENJITH A K wrote: > Hi > When I tried to subtract two double values its giving unexpected > results!!!! > ...

data look up and return values across a row
I have a list in one spreadsheet, we'll call it 'spreadsheet A' that I need to cross reference with another larger spreadsheet, which we'll call 'spreadsheet B'. I'd like to remove everything from B that isn't on A. Is there an easy way of doing that? Chip Pearson has lots of info about working with duplicates at: http://www.cpearson.com/excel/duplicat.htm Betsey wrote: > > I have a list in one spreadsheet, we'll call it 'spreadsheet A' that I need > to cross reference with another larger spreadsheet, which we'll call > &#...

Lookup second/third values
I have a list of items in column a. Every time a value occurs I need to return the value in column B to my sublist in another sheet. Here is what I'm talking about - my list would look like this A B 1 High 2 low 3 Right 1 Left 5 New 1 Old 1 Young 2 Ancient 3 Used Lets say I wanted to display all items from column B where column A = 1. If I use a vlookup, of course it just returns the first value (High) but I need my list on the other sheet to look like this: High Left Old Young I hope this makes sens...

COUNT while eliminating duplicate values
I am trying to get a count for the number of employees in a range but want to eliminate duplicate values. Does anyone know the easiest wa to accomplish this ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com One way: http://j-walk.com/ss/excel/usertips/tip061.htm HTH Jason Atlanta,GA >-----Original Message----- > >I am trying to get a count for the number of employees in a range but I >want to eliminate duplicate values. Does anyone know the easiest way &g...

Looking for basic multiplaction formula
HI ALL, I'm trying to multiply cells d5-d9 by cells a5-a9 and put the answer in cells d14 - d18 ( using caps ) I have tried =(D5*A5) in d14 and then pulling it down to d18 PLEASE HELP ME IVE BEEN AT THIS FOR AN HOUR AND A HALF!! Sounds to me like you had it right... what's not working? In cell D14 enter "=D5*A5" Copy that and paste it in cells D15 to D18. Ken Puls, CMA - Microsoft MVP (Excel) www.excelguru.ca Concarp wrote: > HI ALL, > I'm trying to multiply cells d5-d9 by cells a5-a9 and > put the answer in cells d14 - d18 ( using caps ) > ...

Looking for sheet to handle MPG, etc
HI I am looking for a sheet to handle gas mileage. things like MPG etc. It would be great to have a ready made sheet to handle this info thanks supermari -- Message posted from http://www.ExcelForum.com This shouldn't be too difficult unless you attended college at Texas A&M =miles driven/gallons used -- Don Guillett SalesAid Software donaldb@281.com "supermario >" <<supermario.1a3sk4@excelforum-nospam.com> wrote in message news:supermario.1a3sk4@excelforum-nospam.com... > HI > I am looking for a sheet to handle gas mileage. > things like MPG etc. &g...

Calculate Figures in a column if ajacent column has particular da
How do i total number of cells if the row fall within a particular month and the adjacent column has a "Y" in it. Then Convert this to a 3-d Pie Then the same as above but if the adjacent column is "Blank" instead of a "Y" in it. Shaggy wrote: > How do i total number of cells if the row fall within a particular > month and the adjacent column has a "Y" in it. Then Convert this to a > 3-d Pie > > Then the same as above but if the adjacent column is "Blank" instead > of a "Y" in it. Hi Shaggy, I think you could u...

look up who is connected
hello i would like to check if any pc connects to my xp box which it shares a lot of dirs. can powershell do the job and how? not sure in PS but "netstat" could do this for you. regards Ramazan "ryan" <ryanlihk@hotmail.com> wrote in message news:eKobVuVrKHA.4220@TK2MSFTNGP05.phx.gbl... > hello > > i would like to check if any pc connects to my xp box which it shares a > lot of dirs. > > can powershell do the job and how? thanks On 2/15/2010 2:21 AM, RCan wrote: > not sure in PS but "netstat" could do this...

Look at this package from M$
--ivtelxoethnivzpa Content-Type: multipart/related; boundary="owjfpbnibkp"; type="multipart/alternative" --owjfpbnibkp Content-Type: multipart/alternative; boundary="rubabijkdwz" --rubabijkdwz Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Partner this is the latest version of security update, the "October 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express. Install now to help maintain the security of your computer from these vuln...

Formatting in columns
Hello. I have a report that lists name and phone extension across multiple columns in a report. Is it possible to insert dots between the name and the extension? They are two separate fields from a query. So far I have been unsuccessful. Many thanks for any help. Bird Byte wrote: >Hello. I have a report that lists name and phone extension across multiple >columns in a report. Is it possible to insert dots between the name and the >extension? They are two separate fields from a query. So far I have been >unsuccessful. > Not a simple problem. There are two...