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

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
 
R

RagDyeR

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?
 
P

Peo Sjoblom

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)
 
P

Peo Sjoblom

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...
 
R

Ragdyer

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...
 
P

Peo Sjoblom

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)
 
R

Ragdyer

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.
 
J

judoist

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
 
R

Ragdyer

<<"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
 
J

judoist

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
 
R

RagDyeR

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?
 
H

Harlan Grove

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)
 
J

judoist

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
 
R

RagDyeR

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?
 
J

judoist

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
 
R

RagDyer

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 !
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

making a sum outcome negative based on adjacent cell value 3
formula works but... 1
wrong calculation in excel? formula outcome <> cell value?? 2
refer to same row in other columns by using entire column in formula 2
Creating a huge multiple table diagonally, not row by row or columnby column? 4
IF NEGATIVE VALUE REPLACE WITH POSITIVE 3
adding figures in adjacent cells next to specified numbers 5
this is killing me. INDEX/MATCH "closest to X" in a smart way? 5

Top