[Solved] Find the last 3 non-empty values on a row and ca...
[Solved] Find the last 3 non-empty values on a row and ca...
Hi,
Problem: Find the last 3 non-empty values on a row (or a column) and then calculate the mean value of them.
I can find the last non empty value using INDEX and COUNTA, but that's all. I can't find the second and third before the last. And I think it's not the way.
Any idea?
Would it be possible using formulae? I'm afraid it is only possible using macros.
Thank you very much for your help
Problem: Find the last 3 non-empty values on a row (or a column) and then calculate the mean value of them.
I can find the last non empty value using INDEX and COUNTA, but that's all. I can't find the second and third before the last. And I think it's not the way.
Any idea?
Would it be possible using formulae? I'm afraid it is only possible using macros.
Thank you very much for your help
Last edited by robleyd on Tue Jul 03, 2018 11:59 am, edited 2 times in total.
Reason: Add green tick [robleyd, Moderator]
Reason: Add green tick [robleyd, Moderator]
OpenOffice 4.1 on Ubuntu 16.04
Re: Find the last 3 non-empty values on a row and calculate
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Find the last 3 non-empty values on a row and calculate
Thank for your quick response Villeroy, but with the offset function you have to know the range of the last 3 values. In my case I don't know that range.
I'm attaching an image as example of my problem.
I'm attaching an image as example of my problem.
OpenOffice 4.1 on Ubuntu 16.04
Re: Find the last 3 non-empty values on a row and calculate
The formula in column "P" calculates the average of 3 non-empty cells
- Attachments
-
- Test.ods
- My attempt
- (11.65 KiB) Downloaded 57 times
LibreOffice 7.6.4.1 on Ubuntu 20.04.4 LTS
Re: Find the last 3 non-empty values on a row and calculate
Here is a tip:
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: [Solved] Find the last 3 non-empty values on a row and c
Thank you very much Zizi64, great solution!!!
OpenOffice 4.1 on Ubuntu 16.04
Re: [Solved] Find the last 3 non-empty values on a row and c
Your signature is:
I can not believe that you use this old version of the OpenOffice.org. Please upgrade your office suite, and/or update your signature in this forum.OpenOffice 2.4 on Ubuntu 9.04
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: [Solved] Find the last 3 non-empty values on a row and c
Done! Thx again
OpenOffice 4.1 on Ubuntu 16.04
Re: [Solved] Find the last 3 non-empty values on a row and c
If you don't attach any document, why don't you simply paste your formula at least?
starting with 3 cells A1:C1, 0 rows offset and COUNT()-1 columns offset. Simple as that.
Without the AVERAGE, Min, Max, Sum, whatever,
returns #VALUE for the simple reason that the formula returns 3 values. If you enter this formula as an array formula with Ctrl+Shift+Enter instead of Enter, then you get one formula in 3 cells returning 3 values in a row.
This indicates that your data are adjacent with no gaps and you use something like =INDEX($A1:$L1;COUNT($A1:$L1)) which returns the last numeric value from a sequence with no gaps within A1:L1 (12 cells in a row)rpardeza wrote:I can find the last non empty value using INDEX and COUNTA, but that's all. I can't find the second and third before the last. And I think it's not the way.
Code: Select all
=AVERAGE(OFFSET($A1:$C1 ; 0 ; COUNT($A1:$L1)-1))
Without the AVERAGE, Min, Max, Sum, whatever,
Code: Select all
=OFFSET($A1:$C1 ; 0 ; COUNT($A1:$L1)-1)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Find the last 3 non-empty values on a row and c
Thanks for your help Villeroy.Villeroy wrote:If you don't attach any document, why don't you simply paste your formula at least?This indicates that your data are adjacent with no gaps and you use something like =INDEX($A1:$L1;COUNT($A1:$L1)) which returns the last numeric value from a sequence with no gaps within A1:L1 (12 cells in a row)rpardeza wrote:I can find the last non empty value using INDEX and COUNTA, but that's all. I can't find the second and third before the last. And I think it's not the way.
starting with 3 cells A1:C1, 0 rows offset and COUNT()-1 columns offset. Simple as that.Code: Select all
=AVERAGE(OFFSET($A1:$C1 ; 0 ; COUNT($A1:$L1)-1))
I didn't post a formula because I had any solution then, and I though =INDEX($A1:$L1;COUNT($A1:$L1)) was well known and needed no more indications.
Your formula
Code: Select all
=AVERAGE(OFFSET($A1:$C1 ; 0 ; COUNT($A1:$L1)-1))
Next time I will try to accurate in my descriptions.
Anyway, thanks a lot to everybody in this forum.
OpenOffice 4.1 on Ubuntu 16.04
Re: [Solved] Find the last 3 non-empty values on a row and c
No, simply attach a document with some example data and a short description about what you want to do with next and what you actually tried. Just keep your wrong results in the document. It is far better than any lengthy description.rpardeza wrote:Next time I will try to accurate in my descriptions.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice