Display the second to last non-null cell in a column Solved

Question

Hello,

I asked for help this morning to get a formula that would display the last non-zero cell in a column, and I received some very good advice (even though I admit I applied it a bit without really understanding). I would like to be able to show the second-to-last non-zero cell in my column in the cell next to it, so I can compare my result from this week with last week's result.
I tried to tweak the formula I received this morning, but I can't seem to get my result... :/
I hope I'm not bothering the moderators too much with my somewhat quirky questions, but as a beginner in Excel, I don't see any other way to progress...
To be clear about what I want, here’s an example:
If I have the column:

8798
6545
2123
6545
9878
0
0
0
0

The cell where I apply my formula would return: 6545 because it is the second-to-last non-zero result in my column.
Thank you in advance for your understanding and your great patience ;)

Best regards,

André Roussel

Configuration: Windows / Chrome 59.0.3071.115

Unombre · Answer

Hello,  Try: {=INDEX(A1:A9;MATCH(1,1/(A1:A9>0))-1;0)}

Vaucluse · Answer

Hello

in addition to the formula from Unombre_(Salut) (For your information: your formula is matrix and must be entered by pressing enter while holding down the ctrl and shift keys to automatically be surrounded by brackets _which should not be typed on the keyboard_ and furthermore it seems that the -1 is unnecessary)

if you actually have 0s following your list of numbers, you can also try:

=INDEX(A:A;MATCH(0;A:A;0)-1)
or if your range starts at A2:

=INDEX(A2:A100;COUNTIF(A2:A100;">0"))

as long as in both cases, you do not have any empty cells interspersed within the list of numbers

if there are some empty cells, in matrix form this time you can also use:
=INDEX(A:A;SUMPRODUCT((MAX(IF(A1:A100>0;ROW(A1:A100)))*1)))

but then, it’s better to use Unombre's formula

regards

The quality of the response mainly depends on the clarity of the question, thank you!

Andr&eacute; Roussel · Answer

No, I think I tried it this morning and I just tried it again: it doesn't work :/ (it shows me a 0) But thanks for the response :)

Andr&eacute; Roussel · Answer

I just tried all the formulas you suggested (even the one without the -1) using the keys (ctrl/shift/enter), but none of them give me the second-to-last non-zero result from my column. At worst, it gives me a 0 in my cell, and at best, it gives me the last result from my column...
I don’t know if this plays a role, but my column consists of 7 merged cells per row in my table. (I think it only considers the first one as really filled) So my column is huge and stretches from J14 to J377.
If you need additional information to help me, I'm happy to provide it ;)

Best regards,

André Roussel

Mike-31 · Answer

What cowardice to thank with -1 those who voluntarily want to help.
As for me, in gratitude, I am deleting my post because it is not me who needs help.

See you later
Mike-31

I am responsible for what I say, not for what you understand...

Andr&eacute; Roussel · Answer

Sorry Mike 31,

I recognize that I am a complete beginner (including on the forum); I thought it would help people see what solution works, but I never thought it could penalize you in any way...
I've removed it!
And given how you're helping me, I'm even willing to give you a +1!
But I would really like to find a solution to my problem :)
No offense intended

Best regards,

André Roussel

Andr&eacute; Roussel · Answer

You are right Vaucluse, merged cells are Excel's nightmare!
But the value is entered in the first "box" of each merged cell! So I don't understand why it doesn't work...
In any case, I'm sorry for being so persistent and I thank you again for your patience with such a "pain in the neck" grandfather ;)

Best regards,

André Roussel

Anonymous user · Answer

Hello,
I admit I haven't read everything and especially haven't understood everything.
Just in case, I propose a simple table to build that seems to address the question asked.
http://www.cjoint.com/c/GGojblsnaTf
Happy July 14th!
Best regards,
Alain

gbinforme · Answer

Hello everyone,  If the goal is to "display in my adjacent cell the second-to-last non-empty cell in my column", this simple formula should be sufficient: =OFFSET($A$1,MATCH(0,A2:A200,0)-2,0) -- Always zen Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away. Antoine de Saint-Exupéry

Anonymous user · Answer

Re-hello, A little slip-up, I'm attaching the table in question again. Best regards Alain  http://www.cjoint.com/c/GGoktiq5Adf

Anonymous user · Answer

Our friend Roussel runs away when we call him...

Display the second to last non-null cell in a column

11 réponses

Ulefone armor x stuck at the startup logo

Unable to rename folders and files

Connection issue

Access to a shared mailbox for multiple users

Canon eos 700d memory card not displaying

Tencent impossible to remove

Don't know how to install kudu, no zip no exe???

How can i get the driver?

Lenovo thinkpad battery hs - can i change it myself?

Texture issues in enshrouded