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: [email protected]
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
