Excel: Get location of first and last non-zero cell in row

0
votes

The second row in the screen capture below (row 4 in my sheet) will sum up the values on each column and divide this sum by something. Those values can always change, and I'm trying to find a way to make a selection from the first non-zero value to the last non-zero value so I could use that range, to create a chart.

enter image description here I am using elsewere =LOOKUP(2;1/(4:4<>0);4:4), which will return the last non-zero value of the row number 4, but I cannot use MATCH I guess because I might find other values equal to the last non-zero value, "earlier" in the range, and will not return the reference of that specific last non-zero cell.

I have tried =MATCH(0;4:4;1) to try to find the first non-zero cell, but this will return the location of the last zero cell for some reason.

Any ideas? VBA is also an option.

1
vbaexcel
I you have a spare row then you could add a formula with an IF statement that could put "First" and "Last" above or below your data then use a lookup on those. Something like if(AND(A2=0,B2<>0),"First",if(AND(C2=0,B2<>0),"Last","")) – Gordon
This might be an option but: 1. I would prefer not to create a empty row 2. It is possible also to have some empty cells between the first and the end one than I could have other "First" and "Last" that I don't need. – Angelin Calu

1 Answers

0
votes

What I have been able to find:

First non-zero cell address: {=ADDRESS(4;MATCH(TRUE;4:4<>0;0))}

Last non-zero cell address: {=ADDRESS(4;MAX((4:4>0)*COLUMN(4:4)))}

I have added the { } to point out the fact that the formulas should be confirmed with CTRL + SHIFT + ENTER because they are Array formulas.

Hope it will help others!