Find the last number in a row
in [Functions]
|
Prev: Separate Date from a Text String
Next: Time formula
From: David Brogelli David on 18 May 2010 10:56 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: Don Guillett on 18 May 2010 11:10 If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "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.
From: Luke M on 18 May 2010 11:17 You could use this array* formula: =INDEX(2:2,MAX(IF(B2:Z2<>0,COLUMN(B2:Z2)))) Copy down as needed *Array formulas must be confirmed using Ctrl+Shift+Enter, not just Enter. -- Best Regards, Luke M "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.
From: RagDyeR on 18 May 2010 11:22 This will return the last entry in the range that's greater then 0: =LOOKUP(2,1/(A1:Z1>0),A1:Z1) -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== "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.
From: Mike H on 18 May 2010 11:45
Hi, This finds the 'last' value >0 in the range =LOOKUP(2,1/(A1:L1>0),A1:L1) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "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. |