[Solved] Find the last 3 non-empty values on a row and ca...

Discuss the spreadsheet application
Post Reply
rpardeza
Posts: 6
Joined: Mon Jul 02, 2018 1:42 pm

[Solved] Find the last 3 non-empty values on a row and ca...

Post by rpardeza »

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 ;)
Last edited by robleyd on Tue Jul 03, 2018 11:59 am, edited 2 times in total.
Reason: Add green tick [robleyd, Moderator]
OpenOffice 4.1 on Ubuntu 16.04
User avatar
Villeroy
Volunteer
Posts: 31270
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Find the last 3 non-empty values on a row and calculate

Post by Villeroy »

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
rpardeza
Posts: 6
Joined: Mon Jul 02, 2018 1:42 pm

Re: Find the last 3 non-empty values on a row and calculate

Post by rpardeza »

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.
Attachments
ejemplo.png
OpenOffice 4.1 on Ubuntu 16.04
User avatar
lader
Posts: 46
Joined: Mon Jul 02, 2018 6:10 pm

Re: Find the last 3 non-empty values on a row and calculate

Post by lader »

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
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Find the last 3 non-empty values on a row and calculate

Post by Zizi64 »

Here is a tip:
Average of the last 3 numbers in the row.ods
(10.31 KiB) Downloaded 102 times
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.
rpardeza
Posts: 6
Joined: Mon Jul 02, 2018 1:42 pm

Re: [Solved] Find the last 3 non-empty values on a row and c

Post by rpardeza »

Thank you very much Zizi64, great solution!!!
OpenOffice 4.1 on Ubuntu 16.04
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Find the last 3 non-empty values on a row and c

Post by Zizi64 »

Your signature is:
OpenOffice 2.4 on Ubuntu 9.04
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.
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.
rpardeza
Posts: 6
Joined: Mon Jul 02, 2018 1:42 pm

Re: [Solved] Find the last 3 non-empty values on a row and c

Post by rpardeza »

Done! Thx again
OpenOffice 4.1 on Ubuntu 16.04
User avatar
Villeroy
Volunteer
Posts: 31270
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Find the last 3 non-empty values on a row and c

Post by Villeroy »

If you don't attach any document, why don't you simply paste your formula at least?
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.
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)

Code: Select all

=AVERAGE(OFFSET($A1:$C1 ; 0 ; COUNT($A1:$L1)-1))
starting with 3 cells A1:C1, 0 rows offset and COUNT()-1 columns offset. Simple as that.

Without the AVERAGE, Min, Max, Sum, whatever,

Code: Select all

=OFFSET($A1:$C1 ; 0 ; COUNT($A1:$L1)-1)
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.
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
rpardeza
Posts: 6
Joined: Mon Jul 02, 2018 1:42 pm

Re: [Solved] Find the last 3 non-empty values on a row and c

Post by rpardeza »

Villeroy wrote:If you don't attach any document, why don't you simply paste your formula at least?
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.
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)

Code: Select all

=AVERAGE(OFFSET($A1:$C1 ; 0 ; COUNT($A1:$L1)-1))
starting with 3 cells A1:C1, 0 rows offset and COUNT()-1 columns offset. Simple as that.
Thanks for your help Villeroy.

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))
works fine when you know the range and there are no empty cells in it. But in my case, I have to calculate the average value of the three last values of each row of a pivot table, and the cells maybe empty.

Next time I will try to accurate in my descriptions.

Anyway, thanks a lot to everybody in this forum. :super:
OpenOffice 4.1 on Ubuntu 16.04
User avatar
Villeroy
Volunteer
Posts: 31270
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Find the last 3 non-empty values on a row and c

Post by Villeroy »

rpardeza wrote:Next time I will try to accurate in my descriptions.
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.
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
Post Reply