Another work around at the end.
Dan Lewis wrote:
On Tuesday March 06 2007 11:37 pm, Dwight Brown wrote:
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
Here is an idea. Suppose column E has 15 entries scattered over
200 rows (row numbers are from 4 to 203, and you want to add the
entries in cell E204. Enter this formula into cell E204:
=SUM(E4:E203)
It will add all of the entries and display the total.
If you wanted the total to appear in cell E1 instead of E203
place this same formula in E1 instead of E203.
Furthermore, if you want column totals for columns F and G as
well as E, there is a trick to doing that also. Once you have entered
the formula in the cell you want, click the cell (E204 for example).
A little square appears at the bottom right corner of the cell. It is
called a handle. Click and drag it to the right so that E204, F204,
andG204 are highlighted together. Release the mouse button. All three
columns now have their column totals in row 204.
I also have a personal finance Calc file with many column totals
as well as row totals. I use this method to enter my SUM() function
with its values into one column or row and then drag it until I get
all of the other totals I want in the other rows and columns.
Dan
You have the option also to add information in a new sheet. Suppose you
wanted a sum for cell a24 , a47, b74, c2, d10, etc.
on the new sheet simply add formulas in cell a1 without the quotes of
course "=a24" , in cell a2 "=a47", in cell a3 "=b74", in cell a4 "=c2",
in cell a5 "=d10", etc. etc. etc.
Then, if you want to sum these, use "=sum(a1:a5)" in the cell where you
want the sum to appear. The pattern is important, not the details.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
