SUM(above) bug?

[Ed]

Hello All,

Stumbled across something that looks like a bug. I happened to be using Word
2003 but it's not limited to that version.

A user had the language in Control Panel>Regional and Language
options>Regional Settings & Advanced tabs set to English (United States). It
should have been English (United Kingdom).

She had a table column with something like the following:

6-0002
Blank cell
Some text
Blank cell
£1
£2
Blank cell
{ = SUM(above) }

The formula field at the bottom was doing a couple of odd things; it was
summing all of the numerical values in the column despite the presence of the
blank cells and the cell with text, and it seemed to treat the "6-0002"
(which is a reference number) as a calculation whose result was 4. The
formula field result was 7 (when the desired result was 3).

I changed the "£" signs to "$" signs and the formula field correctly
displayed the result 3.

Put the "£" signs back, set the language in Control Panel to English (United
Kingdom) and rebooted. The formula field then worked correctly.

Tested on my own PC (English (United Kingdom)) and I got the odd behaviour
if I used "$" signs, and got correct behaviour if I used "£" signs.

In the "wrong language setting" scenario, I got different behaviour if I
removed just one of the pound signs but I won't bore you with the details of
that.

So, the presence of the two currency symbols seemed to confuse Word when
they were the "wrong" ones for the language setting.

Anyone come across this before, and if someone needed to use the "wrong"
currency symbols (e.g. an English (UK) setup where dollar calculations were
needed) what would be the best way to do it?

Thanks.

Ed

 

[Graham Mayor]

Not so much a bug as a regional issue allied with Sum Above which at best is
a rather clumsy tool.
If you want to ignore the signs and format the result as dollars then you
would need a different approach

{ =A5 + A6 \# "$,0.00" }

which assumes your two cash amounts are in cells A5 and A6
Note that this is a formatting switch so £1 and £2 would add up to $3!


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Ed]

Hi Graham,

Thanks for the reply.

so £1 and £2 would add up to $3!

Yes That wasn't quite what I had in mind. What I was wondering about
was, for example, a multi-currency scenario where a user might want to
display and sum dollar values and also display and sum (separately) pound
values (or euro values or whatever).

The nice thing about SUM(above) obviously is that you can add rows into the
sum "on the fly" without having to adjust the formula field. A lot of our
clients have system administrators who create the documents, and the
documents are then used by people who don't necessarily know about fields and
stuff.

So, for that sort of reason, it would be nice if the SUM(above) could be
retained along with the currency formatting (but give the right result).

But if it can't, then that's the way it is :-(

Regards.

Ed

 

[Graham Mayor]

You could always perform the calculation in Excel and link the table.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Ed]

Thanks for the suggestion.

In our clients' case, though, I don't think it's a viable option. Most of
their documents are produced automatically from a Case Management system
which automates Word and uses mail merge to pull data in from a database.
Integrating that mechanism with other data in Excel would not be pretty.

If there *were* a very neat and easy way to keep the SUM(above) correct,
that would be good. But it's looking like there isn't and in that case I
suspect that they'll probably just have to live with a (non-ideal)
alternative like using explicit cell references (and giving the users a
little bit of training on that).

So far none of the clients has actually reported the problem, but several of
them are multi-currency businesses and so they might get the problem at some
point. I had thought of using explicit cell references but (because that's
not ideal) I just wanted to check whether there might be a better way.

Regards.

Ed