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
[Solved] Return last non zero in a column
[Solved] Return last non zero in a column
Last edited by MrProgrammer on Tue Nov 02, 2021 5:25 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Reason: Tagged ✓ [Solved]
Open office 4.1.7 on Windows 10
return last non zero in a column
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
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
Re: return last non zero in a column
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: return last non zero in a column
Welcome to the Forums.
By nesting functions, I came up with
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.
By nesting functions, I came up with
Code: Select all
=INDIRECT("A"&TEXT(COUNTIF(A1:A10,">0"),"#"))
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
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.
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.
- MrProgrammer
- Moderator
- Posts: 4895
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Return last non zero in a column
A simple IF() function in the cells of column E (or some other unused column) does this easily. It means: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.
"If the cell below me has a non-zero value, then use that non-zero value, otherwise use the value to my left." [Tutorial] How do I specify the formula for a column?
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.RusselB wrote:=INDIRECT("A"&TEXT(COUNTIF(A1:A10,">0"),"#"))
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).
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).