Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifying the last number in a row.
Hi,
The columns represent the days of the month, i,e, 1 to 31 In a row of cells, some of which contain numbers, and some which are empty, I need to identify the last number in that row with a formula in say column 32. i.e I'm only interested in the last reported number, but it may be in a different column in each row. e.g. A B C D E.... Col32 100 80 40 40 20 30 50 50 Can anyone suggest a formula for column 32 which will result in 40 & 50 in column 32 of the above example. Many thanks, __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifying the last number in a row.
Richard,
Are you wanting a worksheet formula? If so, then try =INDEX(2:2,1,MAX(IF(2:2<"",COLUMN(2:2)))-COLUMN(2:2)+1) it's an array formula, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "Richard Buttrey" wrote in message ... Hi, The columns represent the days of the month, i,e, 1 to 31 In a row of cells, some of which contain numbers, and some which are empty, I need to identify the last number in that row with a formula in say column 32. i.e I'm only interested in the last reported number, but it may be in a different column in each row. e.g. A B C D E.... Col32 100 80 40 40 20 30 50 50 Can anyone suggest a formula for column 32 which will result in 40 & 50 in column 32 of the above example. Many thanks, __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifying the last number in a row.
On Wed, 30 Mar 2005 16:38:49 +0100, "Bob Phillips"
wrote: Richard, Are you wanting a worksheet formula? If so, then try =INDEX(2:2,1,MAX(IF(2:2<"",COLUMN(2:2)))-COLUMN(2:2)+1) it's an array formula, so commit with Ctrl-Shift-Enter Bob, Thanks for this. An array formula would be fine. What are the references to 2:2 in your example? I think I may need to change them to fit my specific worksheet. If it helps the columns I'm trying to include are E:AA - slightly less than 31 because I don't have columns for weekends. Regards, Richard __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifying the last number in a row.
On Wed, 30 Mar 2005 16:38:49 +0100, "Bob Phillips"
wrote: Richard, Are you wanting a worksheet formula? If so, then try =INDEX(2:2,1,MAX(IF(2:2<"",COLUMN(2:2)))-COLUMN(2:2)+1) it's an array formula, so commit with Ctrl-Shift-Enter Bob, Forget my follow up. I see what you mean. The 2s referred to row 2. In my particular example I clearly need to change the 2:2 to a specific range e.g. E2:AA2 That's brilliant. Works perfectly. I just need to sit down and understand it now! Many thanks again, Rgds __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Identifying next number in a range and referencing it | Excel Discussion (Misc queries) | |||
Searching for mulitple strings and assigning identifying number | Excel Worksheet Functions | |||
Identifying | Excel Discussion (Misc queries) | |||
Identifying Top row | Excel Programming | |||
Identifying a value | Excel Programming |