Find the last number in a row
in [Functions]
|
Prev: Separate Date from a Text String
Next: Time formula
From: FSt1 on 18 May 2010 11:52 hi, assuming first row in 2 and formala below in column A, try this formula..... =OFFSET(B2,0,COUNT(B2:L2)-1) Adjust cell references to suit your data. Regards FSt1 "David Brogelli" wrote: > Hi, > > I have lots of rows of data which are either a value between 50 and 200 (e.g > 115) or "0". > > Each column is a specific week of the year, ie from 1/1998 to 52/2009, so > the values run from left to right giving a number for each week, and when > there is not an entry for that week, there is a "0". > > I need a formula to find the last number entry of each row. I could > obviously do this by scrolling to the last week (52/2009) an looking back > until I see the first number which isn't 0, however I need to do this for > around 1600 rows of data. > > I would greatly appreciated it if anybody could help me with this quiery. > > Thanks for your time.
From: T. Valko on 18 May 2010 12:17
This will return the rightmost numeric value that is not 0 from the referenced range: =LOOKUP(1E100,1/B2:Z2,B2:Z2) -- Biff Microsoft Excel MVP "David Brogelli" <David Brogelli(a)discussions.microsoft.com> wrote in message news:B742FE24-DB61-47F6-9A9A-83FAB11A49CC(a)microsoft.com... > Hi, > > I have lots of rows of data which are either a value between 50 and 200 > (e.g > 115) or "0". > > Each column is a specific week of the year, ie from 1/1998 to 52/2009, so > the values run from left to right giving a number for each week, and when > there is not an entry for that week, there is a "0". > > I need a formula to find the last number entry of each row. I could > obviously do this by scrolling to the last week (52/2009) an looking back > until I see the first number which isn't 0, however I need to do this for > around 1600 rows of data. > > I would greatly appreciated it if anybody could help me with this quiery. > > Thanks for your time. |