Locating first cell that has a number in it

[hapster]

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....

 

[Vasant Nanavati]

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(COLUMN(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.

 

[Ron Rosenfeld]

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

 

[hapster]

Thanks for the quick response, but I got an #VALUE error in the cell I put
that in.....

 

[Vasant Nanavati]

Did you:

1. fix the line break?

2. enter it as an array formula with <Ctrl> <Shift> <Enter>?

It works fine on my system!