[Solved] Return last non zero in a column

Discuss the spreadsheet application
Post Reply
mrradio
Posts: 2
Joined: Tue Oct 26, 2021 12:05 am
Location: Ontario Canada

[Solved] Return last non zero in a column

Post by mrradio »

I have tried many of the suggested answers to my problem. I need a function to return the last non-zero value in a specified range eg D10:D20. The values returned in the column are random and do not ascend or descend.

Eg.
D100 ay 5 cells have valid numbers, and the following 5 are zeros, I need the last valid number.
I have a small column of numerical values which are dependent on an adjacent cell.
The adjacent cell is not filled until other data is entered.
So my column results in a zero
Last edited by MrProgrammer on Tue Nov 02, 2021 5:25 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Open office 4.1.7 on Windows 10
mrradio
Posts: 2
Joined: Tue Oct 26, 2021 12:05 am
Location: Ontario Canada

return last non zero in a column

Post by mrradio »

I have a simple column of numbers which can be random not ascending or descending,(say an average).
Until data is entered elswhere, my column calculates a zero value.
I need a function to return the last non zero number in the specified column range eg D10:D20
for example; D10 to D15 are real numbers but the balance D16 to D20 return zeros. I need it to return D15
I have tried Index and a few others from examples here but cant get them to work whenever they encounter a zero.
Thanks
 Edit: Multiple posts by new user merged into the same topic 
Open office 4.1.7 on Windows 10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: return last non zero in a column

Post by FJCC »

The attached file shows one way to get the last nonzero value in the range D10:D20. The formula in E8 is an array formula. I typed it in the usual way but finished the entry with CTRL + Shift + Enter. Your example confused me, so this may not be what you want.
Attachments
LastNonzero.ods
(8.58 KiB) Downloaded 113 times
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: return last non zero in a column

Post by RusselB »

Welcome to the Forums.
By nesting functions, I came up with

Code: Select all

=INDIRECT("A"&TEXT(COUNTIF(A1:A10,">0"),"#"))
Adjust the column letter, A in my code, and range A1:A10 to match your information, but this should work for what you have described.
If it doesn't, attach a sample spreadsheet (see How to attach a document) that shows an instance where it does not work.
Last edited by robleyd on Tue Oct 26, 2021 2:50 am, edited 2 times in total.
Reason: Fix broken link
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Return last non zero in a column

Post by MrProgrammer »

mrradio wrote:I need a function to return the last non-zero value in a specified range eg D10:D20. The values returned in the column are random and do not ascend or descend.
A simple IF() function in the cells of column E (or some other unused column) does this easily. It means:
"If the cell below me has a non-zero value, then use that non-zero value, otherwise use the value to my left."
202110261642.ods
(10.12 KiB) Downloaded 86 times
[Tutorial] How do I specify the formula for a column?
RusselB wrote:=INDIRECT("A"&TEXT(COUNTIF(A1:A10,">0"),"#"))
This solution requires the assumption that no zeros occur before the last non-zero value. It fails for the set 3, 1, 0, 1, 5, 0, 0, 0, 0, 0, selecting A4 (value 1) instead of A5 (value 5). It also requires that the values begin in row 1.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Post Reply