Excell can't add

  • Thread starter Thread starter Vickie
  • Start date Start date
V

Vickie

Often excell does not recognize contents of cell as a
number, so when you use sum it does not add these cells
in. I have tried to use the format comand to make
recognize the cell as a number but it doesen't help. I
have learned to format the cells to be added with 4
zeros. This showes me which cells it isn't adding
because it does not add the zero's to the "bad cells".
As I use this extensivly in my work it is a pain not to
be able to trust the answer it gives you.
 
hi vickie

i don't often have a problem with excel not recognising a number as a
number - are you typing your numbers into the cell or are they the result of
an import / copy & paste procedure or what?

JulieD
 
Your data is part text and part numeric,and Excel is behaving itself in only
adding up numeric data. You cannot just format (Yes I know it's a PITA), a cell
from text to numeric if it already contains data, you have to coerce it. Pick
any empty cell that you know has NOT been formatted as text and then copy it.
Now select all your data and do Edit / Paste Special / Add.

This will coerce the data back to numeric without changing the underlying values
at all.
 
Always check a

=SUM(Range)

with

=SUMPRODUCT(--Range)

When you get a difference between the outcomes or the SumProduct formula
ends up in an error value, you have to check Range for text-formatted
numbers.
 
Good de-bugging technique!

Bob

Aladin Akyurek said:
Always check a

=SUM(Range)

with

=SUMPRODUCT(--Range)

When you get a difference between the outcomes or the SumProduct formula
ends up in an error value, you have to check Range for text-formatted
numbers.
Click to expand...
 
This NG is great, but it's frustrating because you keep discovering things
that you can't find in Help.

If you look for help on "--", it asks you to reformulate your question; what
does "--Range" do?

Concerning the can't add problem, data imported from other "regions" or
applications can cause trouble because they may have a different decimal
separator to yours... because after 1000+ years nobody, not even BG, has
thought of designating a unique symbol for this.

Regards
 
Chris R. Lee said:
This NG is great, but it's frustrating because you keep discovering things
that you can't find in Help.

If you look for help on "--", it asks you to reformulate your question; what
does "--Range" do?
Click to expand...

When you have two idential tokens in sequence, it's always a possibility
that that it means two tokens repeated. Unary minus is a well-know operator,
at least to those with some programming experience. A unary minus following
a unary minus simply reverses the sign again. That is, --x = -(-x) = x, but
with the Excel twist that the first unary minus converts any valid text
representation of a number is converted into that number before its sign is
changed.

Also, when online help is unhelpful, there's always the newsgroup archives
to consult. Lots of explanation of --Range, Range+0, Range*1 and perhaps
even Range^1, Range/1 and Range-0. All accomplish the same thing.
Concerning the can't add problem, data imported from other "regions" or
applications can cause trouble because they may have a different decimal
separator to yours... because after 1000+ years nobody, not even BG, has
thought of designating a unique symbol for this.
Click to expand...

Who needs more characters? All English speaking countries, Japan, India,
Pakistan, Russia and the Arabic speaking countries of western Asia and China
when using Hindu-Arabic numerals use period as the decimal point. That
leaves western and central Europe and countries colonized by them that use
commas. If we're going to be democratic about this, the decision would be
obvious. It seems the only ones who's want a new/different character are
those who can't stand the thought of using period.

As for the thousand years, spelling wasn't standardized in most western
European countries until well into the 18th century, and Hindu-Arabic
numerals weren't widely used until well after 1000 AD, though India, China
and most of east Asia seem to have used decimal fractions for a very long
time. We could always adopt the character the Chinese use to separate
integer and fractional parts giving precedence to the first character in
such use. But that'd be a PITA until all hardware and software supports
Unicode.
 
This will take you to a great many discussions on the unary (--) !

http://tinyurl.com/qvyp

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

This NG is great, but it's frustrating because you keep discovering things
that you can't find in Help.

If you look for help on "--", it asks you to reformulate your question; what
does "--Range" do?

Concerning the can't add problem, data imported from other "regions" or
applications can cause trouble because they may have a different decimal
separator to yours... because after 1000+ years nobody, not even BG, has
thought of designating a unique symbol for this.

Regards
 
Harlan Grove said:
Who needs more characters? All English speaking countries, Japan, India,
Pakistan, Russia and the Arabic speaking countries of western Asia and China
when using Hindu-Arabic numerals use period as the decimal point. That
leaves western and central Europe and countries colonized by them that use
commas. If we're going to be democratic about this, the decision would be
obvious. It seems the only ones who's want a new/different character are
those who can't stand the thought of using period.
Click to expand...

The metric system is quite successfully adopted world-wide. It's now about
time for countries using comma as the decimal separator to switch to period
and to use comma as list separator.

[...]
 
Harlan Grove said:
When you have two idential tokens in sequence, it's always a possibility
that that it means two tokens repeated. Unary minus is a well-know operator,
at least to those with some programming experience. A unary minus following
a unary minus simply reverses the sign again. That is, --x = -(-x) = x, but
with the Excel twist that the first unary minus converts any valid text
representation of a number is converted into that number before its sign is
changed.

Also, when online help is unhelpful, there's always the newsgroup archives
to consult. Lots of explanation of --Range, Range+0, Range*1 and perhaps
even Range^1, Range/1 and Range-0. All accomplish the same thing.
Click to expand...
Thanks. The problem is that some of us work in a regulated environment
(pharmaceuticals, defense, etc.), where you often need to do ad hoc
calculations that would take too long by hand, but don't justify a full IT
development project. Homespun VBA is not really a possibility here because
of the validation issue. Formulae provide a way out, since there are fewer
ways your efforts can go out of control. Since XL is still evolving, there
all sorts of gadgets like this one appear without being documented in a
place that can reliably be cited. It would be great if some expert could
write a manual for a restricted subset of XL formulae, in the style of those
for computing languages like Fortran where the instruction set is limited
and fully defined. Of course this would not prevent other users from using
all the facilities of the most recent version.

Who needs more characters? All English speaking countries, Japan, India,
Pakistan, Russia and the Arabic speaking countries of western Asia and China
when using Hindu-Arabic numerals use period as the decimal point. That
leaves western and central Europe and countries colonized by them that use
commas. If we're going to be democratic about this, the decision would be
obvious. It seems the only ones who's want a new/different character are
those who can't stand the thought of using period.
Click to expand...

The ISO standard specifies the comma, but tolerates the period. It isn't a
question of democracy, but of fatal errors in a multinational environment.
As for the thousand years,
Click to expand...

.... was only referring to the curious fact that while mathemeticians (Arab
as far as I can remember of what I've read) invented the decimal fraction
rather a long time ago, even those countries that use Western symbols havn't
got round to the idea that a unique symbol might be useful.

One purely practical difficulty, not only with XL, is with translating
documents etc. Other contributors have remarked that regional settings
should be applicable to individual files, rather than at the OS level. To
come back to the subject of this NG, it would be particularly useful if what
are patronisingly called "regional" users could switch to the American
version of keywords without affecting other items such as menus.
 
Chris R. Lee said:
Thanks. The problem is that some of us work in a regulated environment
(pharmaceuticals, defense, etc.), where you often need to do ad hoc
calculations that would take too long by hand, but don't justify a full IT
development project. Homespun VBA is not really a possibility here because
of the validation issue. Formulae provide a way out, since there are fewer
ways your efforts can go out of control. Since XL is still evolving, there
all sorts of gadgets like this one appear without being documented in a
place that can reliably be cited. It would be great if some expert could
write a manual for a restricted subset of XL formulae, in the style of those
for computing languages like Fortran where the instruction set is limited
and fully defined. Of course this would not prevent other users from using
all the facilities of the most recent version.
Click to expand...
....

OK, what's to prevent you from doing this?
The ISO standard specifies the comma, but tolerates the period. It isn't a
question of democracy, but of fatal errors in a multinational environment.
Click to expand...

Which ISO standard? Even so, if ISO represents common usage of fewer than
1/3 of living humans, how standard is it?
 
Hi Harlan!

Re: "Which ISO standard?"

I think it's still, ISO 31-0:1992 (E)

Three issues covered:
Decimal sign written as a comma
Digit spacing to be a space
Multiplication sign accepts either a half-high dot or a cross

US NIST has adopted the use of a space for digit spacing:

http://physics.nist.gov/cuu/Units/checklist.html

A space or half-high dot is used to signify the multiplication of
units.

It's silent on use of decimal sign but uses a dot throughout.


Chances of a decimal dot being dropped approach 0!


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Actually, NIST is quite specific:

http://physics.nist.gov/Pubs/SP811/sec10.html
10.5.2 Decimal sign or marker
The recommended decimal sign or marker for use in the United States
is the dot on the line [4, 8]. For numbers less than one, a zero is
written before the decimal marker. For example, 0.25 s is the
correct form, not .25 s.

(Fer cryin out loud, they're *physicists*! They wouldn't leave something
like that to chance - it might lead to, say, miscalculating a trajectory
to Mars or somethin'!)
Chances of a decimal dot being dropped approach 0!
Click to expand...

"approach" == "are indistinguishable from"
 
Hi JE!

Thanks for the reference on that one.

Re the ISO position. The quote I was was:

"In accordance with an ISO Council decision, the decimal sign is a
comma in all ISO documents." ISO 31-0:1992

Now whether that just applies to ISO documents or is a standard, I'm
not so sure. I think it's a Standard where you are in an ISO
compliance situation.

I'll have to get a copy of this Standard some time. (Unfortunately,
you have to pay!)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
JE McGimpsey said:
Actually, NIST is quite specific:

http://physics.nist.gov/Pubs/SP811/sec10.html
10.5.2 Decimal sign or marker
The recommended decimal sign or marker for use in the United States
is the dot on the line [4, 8]. For numbers less than one, a zero is
written before the decimal marker. For example, 0.25 s is the
correct form, not .25 s.

(Fer cryin out loud, they're *physicists*! They wouldn't leave something
like that to chance - it might lead to, say, miscalculating a trajectory
to Mars or somethin'!)
Chances of a decimal dot being dropped approach 0!
Click to expand...

"approach" == "are indistinguishable from"
Click to expand...
 
The fact that ISO is involved confirms that there is a problem (I know the
p-word is forbidden on the other side of the pond).

For those who don't know how these things are dis(organised), ISO proposes
and individual national authorities dispose.

A decimal point on or above the line gets mixed up with ordinary
punctuation, arithmetic multiplication, etc. The point of my original
posting was to point out that it might be a good idea sometime to settle the
issue. Unicode is coming in slowly but surely. There's room in it for a
decimal separator symbol, but I suggest that a problem that should not be
underestimated is finding a typologically attractive one. Page presentation
and layout is a very difficult subject.

Before I stop, I propose that anyone who thinks standardisation easy should
try to get in the habit of writing dates correctly. Today is 2004-03-01 in
ISO and (2-century old) astronomical notation. If you write 01/03/04 (UK,
France), 03/01/04 (the US region), 04/03/01 (Scandanavia and most of Asia),
bonjour mouldy foodstuffs, dud contracts and patents, overdue payments ...
and crashed spaceships.

Regards


Norman Harker said:
Hi JE!

Thanks for the reference on that one.

Re the ISO position. The quote I was was:

"In accordance with an ISO Council decision, the decimal sign is a
comma in all ISO documents." ISO 31-0:1992

Now whether that just applies to ISO documents or is a standard, I'm
not so sure. I think it's a Standard where you are in an ISO
compliance situation.

I'll have to get a copy of this Standard some time. (Unfortunately,
you have to pay!)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
JE McGimpsey said:
Actually, NIST is quite specific:

http://physics.nist.gov/Pubs/SP811/sec10.html
10.5.2 Decimal sign or marker
The recommended decimal sign or marker for use in the United States
is the dot on the line [4, 8]. For numbers less than one, a zero is
written before the decimal marker. For example, 0.25 s is the
correct form, not .25 s.

(Fer cryin out loud, they're *physicists*! They wouldn't leave something
like that to chance - it might lead to, say, miscalculating a trajectory
to Mars or somethin'!)
Chances of a decimal dot being dropped approach 0!
Click to expand...

"approach" == "are indistinguishable from"
Click to expand...
Click to expand...
 
Thanks.

Unfortunately, this is the sort of quick workaround, shortcut or fancy
trick that computer programming textbooks tell you not to use. Perfectly all
right though for a bit of office software that won't last much longer than
the present version of your favorite spreadsheet.

Of course an explicit function that does the same thing would be more
traceable, but you can't have everything. To reply to another suggestion in
this thread, I'm not competent enough to decide what would be the minimal
command set that would allow you to do most things you want in an
environment that has to be be fully validated.

Regards
 
Hi Chris!

I agree with your "rant" but you're on a lost cause.

ISO8601:2000 on dates has been around as a Standard for nearly 15
years. I believe it is being brought into use by the EEC but the only
time I've seen it in Australia is on my visa for China (where they've
used it since time immoral).

The ISO8601:2000 separated date form yyyy-mm-dd does have one use. It
is one of the few datestrings that are correctly interpreted by all
English and foreign language editions of Excel.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi JE!

Stolen from:
1066 And All That by W C Sellar, R J Yeatman
A Memorable History of England, comprising all the parts you can
remember, including 103 Good Things, 5 Bad Kings and 2 Genuine Dates.

But you're right. That why I was sent here!
 
Back
Top