If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Find the last number in a row
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. |
#3
|
|||
|
|||
Find the last number in a row
You could use this array* formula:
=INDEX(2:2,MAX(IF(B2:Z20,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 wrote in message ... 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. |
#4
|
|||
|
|||
Find the last number in a row
Hi,
This finds the 'last' value 0 in the range =LOOKUP(2,1/(A1:L10),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. |
#5
|
|||
|
|||
Find the last number in a row
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. |
#6
|
|||
|
|||
Find the last number in a row
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 wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|