Sunday 15 October 2017

Libreoffice: sum of column except one cell


I want to calculate the sum of the A column excluding A1, because I want the result there. Illustration:


        A                    B      C
1 =SUM(A2:Ainfinite)
2 1234
3 5678
...

I could write A2:A1048576 which would work to some degree, but it would not be elegant or foolproof. Is there any notation to express the range from A 2 to A infinite?



Answer



Ehm, I am risking to say something silly here, but why don't you just


Get total number of rows:


ROWS(A:A)

Convert to coordinate of the last cell using INDIRECT:


(INDIRECT("A"&(ROWS(A:A))))

And use it in your SUM formula


 SUM(A2:(INDIRECT("A"&(ROWS(A:A)))))

I cannot guarantee that this is going to work, as I am currently logged into my Windows machine. But it works on MS Excel.


UPDATE: as correctly noted by tohuwawohu you will need to set formula syntax to Excel A1


No comments:

Post a Comment

Where does Skype save my contact's avatars in Linux?

I'm using Skype on Linux. Where can I find images cached by skype of my contact's avatars? Answer I wanted to get those Skype avat...