finding last value in rows

[pinmaster]

Here's what I have. In column B5 I have a list of 35 people and i
columns C5 through AL5 I have weekly values, all with 0 values excep
the first week which has already been entered. I need excel to find th
last value that is not 0 for each of the 35 people.
I tried this formula but it only seems to work with the first week o
values.

=INDEX('Totals sheet'!C5:AL5,COUNTA('Totals sheet'!C5:AL5)>0)

Can anyone help?...thanks

 

[Frank Kabel]

Hi
one way: enter the following array formula (CTRL+SHIFT+ENTER):
=INDEX('Totals sheet'!C5:AL5,0,MAX(('Totals
sheet'!C5:AL5>0)*ISNUMBER('Totals sheet'!C5:AL5)*COLUMN('Totals
sheet'!C5:AL5)))

 

[pinmaster]

Hi Frank, tried it and getting #REF

 

[Mark Graesser]

Pinmaster

Try this formula, entered as an array (ctrl-shift-enter)

=OFFSET($A$1,ROW(A5)-1,MAX((C5:AL5>0)*COLUMN(C5:AL5))-1,1,1

I assume you will have this formula in the same row as the data

Offset from this cell: $A$
By this many rows: ROW(A5)-
By this many columns: MAX((C5:AL5>0)*COLUMN(C5:AL5))-
range is this high:
and this wide:

Good Luck
Mark Graesse
(e-mail address removed)

----- pinmaster > wrote: ----

Here's what I have. In column B5 I have a list of 35 people and i
columns C5 through AL5 I have weekly values, all with 0 values excep
the first week which has already been entered. I need excel to find th
last value that is not 0 for each of the 35 people
I tried this formula but it only seems to work with the first week o
values

=INDEX('Totals sheet'!C5:AL5,COUNTA('Totals sheet'!C5:AL5)>0

Can anyone help?...thanks

 

[pinmaster]

Hi Mark, no it's not on the same row and not on the same sheet, m
starting cell is D10 in the "standings" sheet, but I can work with wha
you gave me......thanks again!

Jean-Guy Collette
Canad

 

[Dave Peterson]

maybe:

=INDEX('totals sheet'!C5:AL5,MAX(('totals sheet'!C5:AL5>0)
*ISNUMBER('totals sheet'!C5:AL5)
*COLUMN('totals sheet'!C5:AL5)-COLUMN('totals sheet'!$C5)+1))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

 

[pinmaster]

Hi Dave.....works great!.....thanks a lot, much apreciated!

Jean-Guy!