- and + values are incorrect

[Guest]

The following function:

=Abs(Sum(([pres]="scr")*[gain]))

Produces a positive result all the time, even if the answer should be
negative.

I tried the following:

=Sum(([pres]="scr")*[gain])

and it returned a negative value when it should have been positive and
positive when it should have been negative.

 

[John Spencer]

PERHAPS what you are looking for is

Abs(Sum([Pres] = "Scr")) * [Gain]

That counts the number of time Pres = "Scr" and then multiplies that times the
value of gain. If gain is positive you get a positive number, if gain is
negative you get a negative number.

Alternatively, you could take your last method and multiply it by -1 to switch
the sign

= -1 * Sum(([pres]="scr")*[gain])

 

[Guest]

The following worked:

= -1 * Sum(([pres]="scr")*[gain])

Can anyone tell me why I am returning the opposite value without multiplying
my answer by -1?

'pres' is a text field
'gain' is a number field that may be a positive or negative value.

PERHAPS what you are looking for is

Abs(Sum([Pres] = "Scr")) * [Gain]

That counts the number of time Pres = "Scr" and then multiplies that times the
value of gain. If gain is positive you get a positive number, if gain is
negative you get a negative number.

Alternatively, you could take your last method and multiply it by -1 to switch
the sign

= -1 * Sum(([pres]="scr")*[gain])

The following function:

=Abs(Sum(([pres]="scr")*[gain]))

Produces a positive result all the time, even if the answer should be
negative.

I tried the following:

=Sum(([pres]="scr")*[gain])

and it returned a negative value when it should have been positive and
positive when it should have been negative.

 

[Tom Lake]

The following worked:

= -1 * Sum(([pres]="scr")*[gain])

Can anyone tell me why I am returning the opposite value without
multiplying
my answer by -1?

Internally, true values are stored as -1, false as 0.
Since ([pres]=0) is either true or false, it returns 0 or -1.

Tom Lake

 

[Tom Lake]

The following worked:

= -1 * Sum(([pres]="scr")*[gain])

Can anyone tell me why I am returning the opposite value without
multiplying
my answer by -1?

Internally, true values are stored as -1, false as 0.
Since ([pres]=0) is either true or false, it returns 0 or -1.

That should be ([pres]="scr") is either true or false of course.

 

[John Spencer]

Because you are muliplying by a negative number.

[pres]="scr") returns -1 (true) or 0 (False). and Sum of all the -1 is a
negative number.

You could rewrite this to

= Sum(IIF([pres]="scr",1,0)) * [Gain]

or

=Count(IIF([pres]="scr",1,Null)) * Gain

The following worked:

= -1 * Sum(([pres]="scr")*[gain])

Can anyone tell me why I am returning the opposite value without
multiplying
my answer by -1?

'pres' is a text field
'gain' is a number field that may be a positive or negative value.

PERHAPS what you are looking for is

Abs(Sum([Pres] = "Scr")) * [Gain]

That counts the number of time Pres = "Scr" and then multiplies that
times the
value of gain. If gain is positive you get a positive number, if gain is
negative you get a negative number.

Alternatively, you could take your last method and multiply it by -1 to
switch
the sign

= -1 * Sum(([pres]="scr")*[gain])

The following function:

=Abs(Sum(([pres]="scr")*[gain]))

Produces a positive result all the time, even if the answer should be
negative.

I tried the following:

=Sum(([pres]="scr")*[gain])

and it returned a negative value when it should have been positive and
positive when it should have been negative.