LookUp last non zero Value in a Row with blanks

[mahones403]

Hello everyone, sorry for the noob question, but I'm trying to find the the last value in a row that is greater than zero with rows that contain numbers, zeros, and blanks. I need this number so I can match it with the first non zero to calculate growth. I have the first value, and the growth rate formula, I'm just having problems with finding the last non-zero value. The function I'm using is:

=LOOKUP(2,1/(J6:V6>0),J6:V6)

Only problem is if there are blank numbers at the end of the row, it will include them and return a blank as the value.

Say the row is : "","","","",0,0,4,5,6,0,0,""

The first 4 months the publisher wasn't on contract so there are blanks, then if the pub was on contract but generated no revenue that month it's a zero, then the publisher must not be on contract anymore resulting in a blank cell at the end. I need a formula that will return "6" because that was the last amount of revenue the generated in the last month they actually generated some revenue.

[Fotis1991]

Hi

Try this.

=MAX(A1:G1)

=MAX(j6:V6) In your own Sheet.

[mahones403]

Sorry, that was just an example, the final month the pub generated revenue might not necessarily be the MAX amount of revenue they generated. I need the 6 because its the final amount the pub generated revenue, not because it's the most revenue they generated.

[Fotis1991]

...So, we need something more complicate....

Try this Array Formula(Control+Shift+Enter). Not just Enter.

=INDEX(A1:G1;MAX(IF(ISNA(MATCH(IF(A1:G1<>0,A1:G1;"");A1:G1;0));"";MATCH(IF(A1:G1<>0;A1:G1;"");A1:G1;0))))

1) Change the semi-colons to gomma, if you heve to do this.
2) Put your range. I test it with valuew from A1:G1.

[mahones403]

Phenomenal, you are the best!!

[icestationzbra]

@Fotis1991:

if the last two non-zero values are identical, then the MATCH function returns the position of the first match.

for example: consider the array {"","",0,1,1,0}

MATCH will return position 4.

is there any way for this function to return the position of the last non-zero value? this is not related to the OP's question, but something i am trying to understand.

also, in this {0,1,5,0,6,2,0,1,1,0} case, the formula returns 2. do not get me wrong, i am not trying to poke holes into the formula; i am just trying to learn.

thank you.

[Fotis1991]

@icestationzbra

I used this formula first time2,5-3years ago.... Never noticed the point that you noticed!!

...do not get me wrong, i am not trying to poke holes into the formula...i am just trying to learn.

Don't worry. You found the holes that i did not know that exist.....

....also, in this {0,1,5,0,6,2,0,1,1,0} case, the formula returns 2

Yes you are right. So try this Array formula and will gives you 1.

=INDIRECT(ADDRESS(ROW($A$1:$N$1);MAX(($A$1:$N$1<>0)*(COLUMN($A$1:$N$1)))))

...is there any way for this function to return the position of the last non-zero value? this is not related to the OP's question, but something i am trying to understand...

Try this, also Array formula...

=MAX((A1:N1>0)*(COLUMN(A1:N1)))

Hope to give you correct answers in yor questions..

[Pepe Le Mokko]

The following also does the job

=LOOKUP(2,1/((1:1)>0),1:1)

[Haseeb Avarakkan]

You could also use,

=LOOKUP(2,1/ISNUMBER(1/J6:V6),J6:V6)

[daddylonglegs]

Try this

=1/LOOKUP(99^99,1/J6:V6)

[icestationzbra]

@PLM / HA / DLL:

un-friggin-believable... my jaw dropped increasingly as i went down the thread...