What formula to use if function cant sustain the list? Lookup doesn't help

[bigjohn]

Said there is a set of data of 1 column and 60 rows.

The "if" function is connected in such manner.

=if(A60>0,A60,if(A59>0,A59,if(A58>0,A58 and so on.

The problem is "if" can't fulfill all the condition.

If I use the lookup, there is not exactly number to look for

because the 60 rows of number is not in range number.

How can the problem to solve?

 

[Luke M]

Is the data sorted? If so, we can do something like

=INDEX(A1:A60,MATCH(1E-99,A1:A60,-1))


If it's not sorted, we could do this array:

=INDEX(A:A,MAX(IF(A1:A60>0,ROW(A1:A60))))

Remeber to confirm an array formula using Ctrl+Shift+Enter not just Enter.

 

[bigjohn]

All the data is not sorted and filled one by one in order throughout.


1 3000

2 2950

3 2700

4 3000

.

.

.

59 2350

60 2400


if there is not data in row 60, the formula should show data in row 59. The first formula really help to solve the problem.


Do you mind explain the 1E-99? I would like to understand the formula.

 

[Debraj]

Hi bigjohn!,


If I have understand your query correctly.. then you want to fill all blank rows with the value, just over it.. am I correct?


Something like in B2, you want A2 or A1 if A2 is blank..

=IF(ISBLANK(A2),A1,A2)

If its the case then.. you can also..

Select A1:A60, Press Alt + E + G + S + K (Goto Special > Select Blank Cells).

now write "=" and press UP Key.. then Press Ctrl + Enter.. It will also fill all Blank Cell's with the Value just Above it..


Waiting for your Feedback..

Regards,

Deb

 

[bigjohn]

Hi Roy,


The formula is separated from the data column.


Luke M's formula works for me.

 

[Luke M]

Hi bigjohn!

The 1E-99 is an arbitrary small number slightly greater than 0. It is written using XL scientific notation, representing 1x10^(-99). I'm using it in the formula to help find the last cell in the column that is greater than 0. I picked number that I was reasonably sure would be smaller than any of your actual data, but still greater than 0.