Re: [libreoffice-users] Is there an easy way to sum across a gap?

At 18:19 15/02/2017 -0500, Robert Peirce wrote:
> I have data in a table by month. At the end of each year there is a 
> summary row separated by a blank line above and below. Frequently I 
> need to sum from, say, 03/16-02/17 so I have 10 rows with data I 
> want, three rows with data I don't want and two more rows with data 
> I want. My approach has been something like this:
>
> sum(A3:A12)+sum(A16:A17)

This is unnecessarily complicated. Use =SUM(A3:A12,A16:A17)

> A13:A15 contain the data I don't want but I want to be able to see. 
> This works but it is less than automatic. When I copy the formulae I 
> need to edit them so they apply to the right rows.

I'm not sure why you are copying the formula. If you construct it 
properly - with $ signs in the right places - it ought to modify 
itself appropriately. But you can anyway form such a formula very 
simply: type "=SUM(", drag across A3:A12, type ",", drag across 
A16:A17, type ")", press Enter of click the green arrow.

> Another thing is the rows summed aren't always the same. Sometimes I 
> may need only two rows and other times maybe as many as 12.

There was I, thinking most years had twelve months! If you always 
have twelve rows for the months, your SUM() function will add zeros 
for any empty cells and still give the correct answer.

The solution to all this is very probably to design your spreadsheet 
to make the process of creating formulae easy. One obvious 
possibility would be to run your months sequentially, not breaking 
the lists for the annual sums. (After all, January follows December 
just as surely as December follows November.) Your sums could be in a 
new, adjacent column - perhaps next to the December values. Again, 
with appropriately constructed formulae, everything will copy 
seamlessly. Alternatively, you could have a separate table of annual 
sums in consecutive rows somewhere else on the spreadsheet.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted