Hi to all

I'm looking for a non VBA solution (macros not allowed) for the following problem:

Given: table with 3 columns. In columns A and B values can be entered but need not. Possible values in A and B: positive, negative, decimals and zero). The result of the calculation in column C is =(b1-a1) \# "+0,00;-0,00;0,00.

Challenge: The result must not be displayed if one (or both) of the cells A1 or B1 is empty. So I tried:

{ IF { = AND ( { = ABS(b1) } > 0 , { = ABS(a1) } > 0) } = 1 "{ =(b1-a1) \# "+0,00;-0,00;0,00 }" }


Works fine unless A1 and B1 are zero ("0") because then AND will be false. So I tried some calculation and set the condition to ">= 0" expecting empty fields would throw some error:



{ IF { = AND ( { = ABS(b1*1) } >= 0 , { = ABS(a1*1) } >= 0) } = 1 "{ =(b1-a1) \# "+0,00;-0,00;0,00 }" }


Unfortunately, Word does calculate with empty fields, it simply assumes "0". So there are equal results with empty cells and cells containing "0".

Do you see any chance to test cells on empty (or not numeric) just using fields? I don't.

Thanks for any help
NP

A word formula using a cell reference cannot differentiate between 0 and empty. However, it can be done if the ranges are bookmarked and you test the bookmark contents. The easiest way to do that is to use formfields for the data entry in a document using forms protection. That also gives you the advantage of being able to use the formfields' 'calculate on exit' property to auto-update the calculations.

In a generalised sense, the calculation field to evaluate the content is:
{IF{REF MyFld} = {=MyFld} "{MyFld} is Numeric" "{MyFld} is Text"}}
where 'MyFld' is the formfield's internal bookmark name.

Thus, for your purposes, the field might be coded as:
{IF{REF Text1} = {=Text1} {IF{REF Text2} = {=Text2} {={Text1}-{=Text2} \# "+0,00;-0,00;0,00"}}}
where 'Text1' & 'Text2' are the formfields' internal bookmark names.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi,

What a great solution .

Thanks, Paul

NP

Sorry for re-opening this thread, but ...

... to work with negative values the {REF Text1} = {=Text1} part has to be formatted the same way, the FormField is formatted: e.g. {REF Text1} = {=Text1 \# "0,00"}. Otherwise IF will return FALSE.

That's at least what I experienced. Now each value is accepted and calculated the way it should be.

Cheers
NP

True, if you have a formatted number, the switch on the right side of the test needs to mirror that.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]