At 10:04 02/04/2018 -0400, Carol Noname wrote:
adding up columns in excel
If you are adding up columns in Microsoft Excel, you may need to look
elsewhere for help. Perhaps you mean in an OpenOffice spreadsheet?
I am trying to download an excel spread sheet from ebay. Every time
I do, the column with prices has an apostrophe before the number so
I cannot add up the columns.
If you mean that you can see the apostrophes in the cells themselves,
that is very odd. But you probably mean that when a cell value is
displayed in the Input Line it shows with a leading apostrophe: that
is a standard part of spreadsheet behaviour. This indicates that what
you have in a cell with a numeric format (including a currency one)
is actually text - which may look like a number - and not a genuine
number. This may be because the author has intentionally entered text
instead of numbers, has entered currency amounts in what is not the
standard currency for his or her locale, or because of the way in
which you have incorporated the material into your spreadsheet document.
If the distinction between number and text which looks like a number
is not obvious, just consider your (US) zip code, which you quote as
"01373". That is text. If it were a number, you could quote it
instead as 1373 - but you cannot. If it were a number, you could read
it as "one thousand, three hundred [and] seventy-three" - but you'd
never do that. If it were a number, there would be some special
significance in the postal location that happened to have exactly
twice its value - 02746 - but there isn't. After all, it is called a
"zip code", not a "zip number". So there *is* a use for text which
happens to look like a number - not what you want here.
My older version didn't do this.
I hope it did, as this is correct spreadsheet behaviour. This
spreadsheet document may be different from previous ones, or you may
have incorporated the material in a different way.
Please help!
o If you are pasting material into a spreadsheet, you should see the
Text Import window. Tick the "Detect special numbers" box and your
text values will be converted to genuine numbers. (This works for
currency values as well as for numbers.) But this won't help if you
are actually downloading and opening a spreadsheet document, as you say.
When you have text values in your spreadsheet, there are various ways
to convert them to real numbers:
o You can use the VALUE() function to derive the values you need into
a separate column. If you wish, you can then paste them back over the
originals, but using Edit | Paste Special (or Ctrl+Shift+V) instead
of ordinary Paste, and ensuring the Formulae is *not* ticked in the
Paste Spacial dialogue. You may need to format the cells as Currency
in order to add or replace the currency symbol.
o Select the material. Go to Data | Text to Columns... . (The "Detect
special numbers" box should be ticked.) Your text values will be
converted to numbers.
But here is an interesting trick: you can add the text values without
converting them to numbers. If you want to add up, say, the values in
A1 to A99, you might use the formula
=SUM(A1:A99)
As you have found, if you use that with your text values masquerading
as numbers, it will not work. How about incorporating the VALUE()
function to convert the values on the fly? You might try
=SUM(VALUE(A1:A99))
- but that doesn't work either, as the VALUE() function cannot take a
range as its parameter. But what does work is this: enter
=SUM(VALUE(A1:A99))
as the formula in the cell, but instead of pressing Enter or the
(green tick mark) Accept button in the Input Line to complete the
entry, press Ctrl+Shift+Enter. This creates an array formula, which
does work. (You will need the result cell to be formatted as Currency
for its value to behave correctly.) Note that when you have done
this, your formula will appear in the Input Line surrounded by braces, i.e. as
{=SUM(VALUE(A1:A99))}
- but you *cannot* achieve the effect by entering these yourself: you
must instead use Ctrl+Shift+Enter to complete the formula.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
For additional commands, e-mail: users-h...@openoffice.apache.org
