Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locating first cell that has a number in it
I have a large spreadsheet with scattered amounts in cells; i.e., going
across from left to right, JAN, FEB, MAR, APR, etc. Each customer has his own row and some customers have balances in the different months, different from other customers; i.e., Joe Blow will have a balance current up to March, and then nothing from March to July -- or to December for that matter -- while Fred Smith will have a balance from January and a balance every month for the whole year. If there is no balance for the month, the cell is blank. I want to put the latest NONZERO amount in a column to the right of December with the most current NONZERO amount in that column. In other words, if the latest amount for Smith is in April and then from May to the end of the year it's blank, I want April's amount in the column. If the latest amount for Jones is in June, I want that amount in the column. My question is: What is the formula I need to use to search LEFT in a row for the first nonzero amount, skipping blank cells?? Thanks in advance.... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locating first cell that has a number in it
Messy, and I'm sure there's a much easier way, but I can't figure it out at
the moment: =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,MAX(CO LUMN(B2:M2)*(B2:M2<""))-C OLUMN()) entered as an array formula with <Ctrl <Shift <Enter. Assumes the 12 months are in columns B to M. -- Vasant "hapster" wrote in message ... I have a large spreadsheet with scattered amounts in cells; i.e., going across from left to right, JAN, FEB, MAR, APR, etc. Each customer has his own row and some customers have balances in the different months, different from other customers; i.e., Joe Blow will have a balance current up to March, and then nothing from March to July -- or to December for that matter -- while Fred Smith will have a balance from January and a balance every month for the whole year. If there is no balance for the month, the cell is blank. I want to put the latest NONZERO amount in a column to the right of December with the most current NONZERO amount in that column. In other words, if the latest amount for Smith is in April and then from May to the end of the year it's blank, I want April's amount in the column. If the latest amount for Jones is in June, I want that amount in the column. My question is: What is the formula I need to use to search LEFT in a row for the first nonzero amount, skipping blank cells?? Thanks in advance.... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locating first cell that has a number in it
On Fri, 12 Dec 2003 03:41:25 GMT, "hapster" wrote:
I have a large spreadsheet with scattered amounts in cells; i.e., going across from left to right, JAN, FEB, MAR, APR, etc. Each customer has his own row and some customers have balances in the different months, different from other customers; i.e., Joe Blow will have a balance current up to March, and then nothing from March to July -- or to December for that matter -- while Fred Smith will have a balance from January and a balance every month for the whole year. If there is no balance for the month, the cell is blank. I want to put the latest NONZERO amount in a column to the right of December with the most current NONZERO amount in that column. In other words, if the latest amount for Smith is in April and then from May to the end of the year it's blank, I want April's amount in the column. If the latest amount for Jones is in June, I want that amount in the column. My question is: What is the formula I need to use to search LEFT in a row for the first nonzero amount, skipping blank cells?? Thanks in advance.... If your monthly data is in B2:M2, then the array-entered formula: =IF(COUNT(B2:M2)=0,"",INDEX(A2:L2,,MAX((B2:M2<0)* COLUMN(B2:M2)))) gives a blank if there are no entries in the row; otherwise the latest non-zero entry. To ARRAY-ENTER a formula, hold down <ctrl<shift while hitting <enter. --ron |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locating first cell that has a number in it
Thanks for the quick response, but I got an #VALUE error in the cell I put
that in..... "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... Messy, and I'm sure there's a much easier way, but I can't figure it out at the moment: =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,MAX(CO LUMN(B2:M2)*(B2:M2<""))-C OLUMN()) entered as an array formula with <Ctrl <Shift <Enter. Assumes the 12 months are in columns B to M. -- Vasant |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locating first cell that has a number in it
Did you:
1. fix the line break? 2. enter it as an array formula with <Ctrl <Shift <Enter? It works fine on my system! -- Vasant "hapster" wrote in message ... Thanks for the quick response, but I got an #VALUE error in the cell I put that in..... "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... Messy, and I'm sure there's a much easier way, but I can't figure it out at the moment: =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,MAX(CO LUMN(B2:M2)*(B2:M2<""))-C OLUMN()) entered as an array formula with <Ctrl <Shift <Enter. Assumes the 12 months are in columns B to M. -- Vasant |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locating a specific cell | Excel Discussion (Misc queries) | |||
locating the cell that contains the MIN or MAX | Excel Discussion (Misc queries) | |||
locating the top 5 number (in a col) | Excel Worksheet Functions | |||
Locating Last Nonzero Cell in a Row | Excel Worksheet Functions | |||
Locating first blank cell | New Users to Excel |