using value in 3rd column to decide if outcome is positive or negative

  • Thread starter Thread starter judoist
  • Start date Start date
J

judoist

I have 3 columns of single figures. At present i'm using the sumproduc
fuction to multiply and total the figures in column A that fall betwee
4 and 9 with the adjacent figure in column B...

=SUMPRODUCT(--($A1:$A600>=4),--($A1:$A600<=9),($B1:$B600),($A1:$A600)

I'd like to add column C to the formula, so that if it contained
value of -1, 1 or 2, the sum of the adjacent figures in columns A and
appears as a negative number.

For example

A3= 7, B3= 2, C3= 1 Outcome= -14

A4= 9, B4= 1, C4= 5 Outcome= 9

A5= 3, B5= 2, C5= 2 No sum because figure in column A doe
not fall between 4 and 9.

Any ideas
 
Try this:

=SUMPRODUCT((A1:A600>=4)*(A1:A600<=9)*A1:A600*B1:B600)-(SUMPRODUCT((A1:A600>
=4)*(A1:A600<=9)*(C1:C600={-1,1,2})*A1:A600*B1:B600))*2
--

HTH,

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

I have 3 columns of single figures. At present i'm using the sumproduct
fuction to multiply and total the figures in column A that fall between
4 and 9 with the adjacent figure in column B...

=SUMPRODUCT(--($A1:$A600>=4),--($A1:$A600<=9),($B1:$B600),($A1:$A600)

I'd like to add column C to the formula, so that if it contained a
value of -1, 1 or 2, the sum of the adjacent figures in columns A and B
appears as a negative number.

For example

A3= 7, B3= 2, C3= 1 Outcome= -14

A4= 9, B4= 1, C4= 5 Outcome= 9

A5= 3, B5= 2, C5= 2 No sum because figure in column A does
not fall between 4 and 9.

Any ideas?
 
One possible way

=(-(C1<=2)+(C1>2))*SUMPRODUCT(--($A1:$A600>=4),--($A1:$A600<=9),$B1:$B600,$A
1:$A600)

assuming that there can't be -2 etc in C

Although your own formula returns 23 and not 14 for the first row

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Or rather that if there is a <-1 it will still be negative value

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



Peo Sjoblom said:
One possible way
Click to expand...
=(-(C1 said:
1:$A600)

assuming that there can't be -2 etc in C

Although your own formula returns 23 and not 14 for the first row

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
Click to expand...
 
Shouldn't the OP's example equate to a minus 5 (-5) Peo?
Or am I wrong?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Peo Sjoblom said:
Or rather that if there is a <-1 it will still be negative value

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
Click to expand...
 
If you use his data and his formula without involvement of column C,
it will return

23
9
0

now I got the impressions that if there is <=2 in C the result should be
negative and
2 it should be positive without any other interference, so anything in C
Click to expand...
does not change the
results except turning it negative if less than or equal to 2. Of course I
have been wrong before.
We won't know until he's back from the judo training


--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Be careful Peo.
Remember what I stirred up the last time I commented on the OP's handle
relating to skiing and trees.
I still feel bad about that one.
 
Hi

Thanks for your help...

I wanted to be able to choose a specific value in column C to decid
whether sum was positive or negative. In this case, -1,1 or 2 (but no
zero, which should still return a positive value).

The following solution appears to work...

=SUMPRODUCT((A1:A600>=4)*(A1:A600<=9)*A1:A600*B1:B600)-(SUMPRODUCT((A1:A600>=4)*(A1:A600<=9)*(C1:C600={-1,1,2})*A1:A600*B1:B600))*2

... but i have one more problem.

Column A not only contains digits, but also X's. If the X's ar
removed, the above formula works fine. Unfortunately, as soon as an
is introduced the result is #VALUE!.

X, by the way represents the value 1 so i'd like this to be taken int
account as follows...

A1= X, B1= 2, C1= -1... result is -2
A2= X, B2= 1, C2= 5 ... result is 1
A3= X, B3= 1, C3= 2 ... result is 1

Does this make sense
 
<<"I wanted to be able to choose a specific value in column C to decide
whether sum was positive or negative">>
Click to expand...

You could replace {-1,1,2}with a cell address into which you could enter
your "specific value", say D1 for example.

<<"Column A not only contains digits, but also X's">>
<<"X, by the way represents the value 1">>

I think that you're pretty smart.

If you had posted all these variables at the beginning, I doubt if you would
have received any answers.

This is a complicated "mess"!
BUT, it *does* work!

=SUMPRODUCT((A1:A600>=4)*(A1:A600<=9),A1:A600,B1:B600)-(SUMPRODUCT((A1:A600>
=4)*(A1:A600<=9)*(C1:C600=D1),A1:A600,B1:B600))*2+SUMIF(A1:A600,"X",B1:B600)
-(SUMPRODUCT((A1:A600="X")*(C1:C600=D1),B1:B600))*2
 
Thanks

... this works fine!

Is it possible though, to have more than one specific number in D1 a
the same time (say, -1,1 and2) so that all calculations are don
simultaneously
 
This will give you the option to enter "specific numbers" in three different
cells, D1,D2, and D3.

=SUMPRODUCT((A1:A600>=4)*(A1:A600<=9),A1:A600,B1:B600)-(SUMPRODUCT((A1:A600>
=4)*(A1:A600<=9)*((C1:C600=D1)+(C1:C600=D2)+(C1:C600=D3)),A1:A600,B1:B600))*
2+SUMIF(A1:A600,"X",B1:B600)-(SUMPRODUCT((A1:A600="X")*((C1:C600=D1)+(C1:C60
0=D2)+(C1:C600=D3)),B1:B600))*2
--

HTH,

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

Thanks

... this works fine!

Is it possible though, to have more than one specific number in D1 at
the same time (say, -1,1 and2) so that all calculations are done
simultaneously?
 
RagDyeR said:
This will give you the option to enter "specific numbers" in three different
cells, D1,D2, and D3.

=SUMPRODUCT((A1:A600>=4)*(A1:A600<=9),A1:A600,B1:B600)-(SUMPRODUCT((A1:A600

=4)*(A1:A600<=9)*((C1:C600=D1)+(C1:C600=D2)+(C1:C600=D3)),A1:A600,B1:B600)) *
0
0=D2)+(C1:C600=D3)),B1:B600))*2
Click to expand...

Looks like you could shorten this to

=SUMPRODUCT(1-2*((C1:C600=D1)+(C1:C600=D2)+(C1:C600=D3)>0),
CHOOSE(1+(A1:A600="X")+2*(A1:A600>=4)*(A1:A600<=9),0,1,A1:A600),
B1:B600)
 
Fantastic...

Thanks for your help!

RagDyeR's solution works but Harlan's returns #VALUE!

Not sure why.

If i wanted to complicate things further by choosing a specific numbe
in column C (say 5) and ignoring any sum in relation to that figure
would that be possible
 
I know if I hardcoded "5" into the formula, you would come back with
*another* request to make it variable.

SO, if you promise that this is the *last* variable! <g> enter the
"DoNotSum" number in D4, and try this:


=SUMPRODUCT((A1:A600>=4)*(A1:A600<=9)*(C1:C600<>D4),A1:A600,B1:B600)-(SUMPRO
DUCT((A1:A600>=4)*(A1:A600<=9)*((C1:C600=D1)+(C1:C600=D2)+(C1:C600=D3)),A1:A
600,B1:B600))*2+SUMPRODUCT((A1:A600="X")*(C1:C600<>D4),B1:B600)-(SUMPRODUCT(
(A1:A600="X")*((C1:C600=D1)+(C1:C600=D2)+(C1:C600=D3)),B1:B600))*2
--

HTH,

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



Fantastic...

Thanks for your help!

RagDyeR's solution works but Harlan's returns #VALUE!

Not sure why.

If i wanted to complicate things further by choosing a specific number
in column C (say 5) and ignoring any sum in relation to that figure,
would that be possible?
 
Thanks RagDyeR...

and YES, that was the last one.

You've saved me hours of extra work, and for that i'm very grateful.

I've learnt a lot since becoming a member of this forum, but can you o
anyone suggest a good book that specialises in the formula creatio
side of Excel?

Excel is clearly capable of a lot more than i imagined and i'd like t
learn more.

Thanks again
 
Thanks for the feed-back ... FINALLY !<g>

Debra Dalgleish has a pretty thorough bibliography on her web site at:

http://www.contextures.com/xlbooks.html

I personally have only used the books by John Walkenbach which I've found
very helpful.
--

HTH,

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


Thanks RagDyeR...

and YES, that was the last one.

You've saved me hours of extra work, and for that i'm very grateful.

I've learnt a lot since becoming a member of this forum, but can you or
anyone suggest a good book that specialises in the formula creation
side of Excel?

Excel is clearly capable of a lot more than i imagined and i'd like to
learn more.

Thanks again !
 
Back
Top