Dan Lewis wrote:
On Monday March 05 2007 9:44 pm, Dwight Brown wrote:
-----Original Message-----
From: [EMAIL PROTECTED]
To: [email protected]
Sent: Sun, 4 Mar 2007 10:31 PM
Subject: [users] [moderated]only 30 numbers can be added in a
column? Any fix?
Is there any way to get around the apparent limitation of only 30
numbers can be added in a column?
Later - I got one response which claims I can add as many numbers
as I wish. Since I was not able to do this I called up "sum" in
NeoOffice: SUM
Adds all the numbers in a range of cells.
Syntax:
SUM(number1; number 2; ...; number 30)
Number 1 to number 30 are up to 30 arguments whose sum is to be
calculated. Example:
If you enter the numbers 2; 3 and 4 in the Number 1; 2 and 3 text
boxes, 9 will be returned as the result.
SUM(A1;A3;B5) calculates the sum of the three cells. SUM (A1:E10)
calculates the sum of all cells in the A1 to E10 cell range.
Conditions linked by AND can be used with the function SUM() in the
following manner:
Example assumption: You have entered invoices into a table. Column
A contains the date value of the invoice, column B the amounts. You
want to find a formula that you can use to return the total of all
amounts only for a specific month, e.g. only the amount for the
period >=1.1.99 to <1.2.99. The range with the date values covers
A1:A40, the range containing the amounts to be totaled is B1:B40.
C1 contains the start date, 1.1.99, of the invoices to be included
and C2 the date, 1.2.99, that is no longer included.
Enter the following formula as an array formula:
=SUM((A1:A40>=C1)*(A1:A40<C2)*B1:B40)
In order to enter this as an array formula, you must press the
Shift + Ctrl + Enter key instead of simply pressing the Enter key
to close the formula. The formula will then be shown in the Formula
bar enclosed in braces.
{=SUM((A1:A40>=C1)*(A1:A40<C2)*B1:B40)}
The formula is based on the fact that the result of a comparison is
1, if the criterion is met and 0 if it is not. The individual
comparison results will be treated as an array and used in matrix
multiplication, and at the end the individual values will be
totaled to give the result matrix. The SUM() function can also be
used in this way, for example, as COUNTIF() with several criteria.
I looked also at Open Office for the Sum function but found the
copy function very unstable. It would not show the whole article,
identical to the one above from NeoOffice and it would not copy at
all. In any case, my work around for this is to select each cell I
want to include in he total and at the bottom of the screen there
is a Total box which actually gives me the total of every box I
selected, but I have figured out how to get this in a box at
thebottom of he column. I realize we are inbeta on this program,
and I do not know whether my experience is peculiar to my machine
(a nerw iMac, Intel) but thought I would pass it along. Dwight
Brown
I am thinking that you have not completely grasped the SUM()
function. There is a limit to the number of entries you can place
into the SUM() function: 30. But this is 30 individual cells or cell
ranges. You can use the SUM() function to add a single range of cells
that consistes of many more cells than 30. For example, I have a list
of donations made to our cemetery fund at our church. There are 166
entried in cells E2 through E167. The sum is calculated by this
formula: =SUM(E2:E167). Notice that these is only "one" entry" in
this case.
Dan
Thanks for this reply. When I have a contiguous list of numbers I can
add them OK but I am working with columns in a bookkeeping spreadsheet
and there may be 15 or 20 numbers in a system which has over 200 cells
in a column, most of them empty. The sum function is drawn to the
adjacent column, the first column I am trying to add, and I cannot get
the program to look up for 10 or more blanks cells to find the
occasional cell with a value in it - I selected every way I could think
of, and kept getting cell references to the first column I added.
Finally I figured out that if I simply drop the cursor on any cell in
the column which has a value, I get a line at the bottom, not in the
spreadsheet, which indeed sums up the cells on which I had dropped the
cursor. Trouble is, I could not figure out how to get this number up
into the spreadsheet(except just copy is by hand,which is subject to
error) to form a row of column totals which could then be added by the
same method (drop cursor on cell with value) in order to confirm that
the total entries in the columns equaled the balance given by the bank.
I believe, in short, that the sum function has bias to look for the
nearest number, and in addition has a problem with adding non-sequential
collections of numbers, where there are large gaps between one number
and the next one..
I would be willing to send this spreadsheet, even though it has my
personal financial information for a month, if you would like to
experiment with it directly. Perhaps you could determine either some
better way for me to get the results I need, OR you could get credit for
reporting a legitimate had shelled animal.
Dwight Brown
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
