help - follow up

N

nca77

hey all,

thanks for the help last week.. it was great!

the formula worked great but now i'd like to tweak it..

basically i paste data in A2:C5000

and have dates D1:BL1

then these 2 formulas:

=-(SUMPRODUCT((DATE(YEAR($C$2:$C$5000),MONTH($C$2:$C$5000),DAY(D$1))=DATE(YEAR(D$1),MONTH(D$1),DAY(D$1)))*$B$2:$B$5000))


=IF(D2=0,"0",(SUMPRODUCT((DATE(YEAR($C$2:$C$5000),MONTH($C$2:$C$5000),DAY(D$1))=DATE(YEAR(D$1),MONTH(D$1),DAY(D$1)))*$B$2:$B$5000*$A$2:$A$5000)/SUMPRODUCT((DATE(YEAR($C$2:$C$5000),MONTH($C$2:$C$5000),DAY(D$1))=DATE(YEAR(D$1),MONTH(D$1),DAY(D$1)))*$B$2:$B$5000))/100)

everything works good but i'd like the formula to include values i
A2:C5000 only if they are positive numbers.

Thanks in advance

nic
 
D

Dave Peterson

You can just add another factor within your giant formulas:

=-(SUMPRODUCT((DATE(YEAR($C$2:$C$5000),MONTH($C$2:$C$5000),DAY(D$1))
=DATE(YEAR(D$1),MONTH(D$1),DAY(D$1)))*$B$2:$B$5000*(B2:B5000>0)))

But if you're only testing for month and year, this might be easier to see:

=-(SUMPRODUCT((TEXT($C$2:$C$5,"yyyy-mm")=TEXT($D$1,"yyyy-mm"))
*$B$2:$B$5*(B2:B5>0)))

(I didn't copy that longer formula, but I bet you see how to adjust it.)
 
N

nca77

thanks for the response.

the formula you provided works for for positive numbers in column B bu
not for column A.

for instance, if there is a negative value in EITHER column A or B, i
should be totally excluded in the calculation.

Also, I was not able to apply your shorter version to the longer secon
formula.

thanks again,

nic
 
D

Dave Peterson

Just keep adding more factors:

=-(SUMPRODUCT((DATE(YEAR($C$2:$C$5000),MONTH($C$2:$C$5000),DAY(D$1))
=DATE(YEAR(D$1),MONTH(D$1),DAY(D$1)))*$B$2:$B$5000*(B2:B5000>0)*(A2:A5000>0)))

and

=IF(D2=0,"0",(SUMPRODUCT((DATE(YEAR($C$2:$C$5000),
MONTH($C$2:$C$5000),DAY(D$1))=DATE(YEAR(D$1),
MONTH(D$1),DAY(D$1)))*$B$2:$B$5000*$A$2:$A$5000*
($A$2:$A$5000>0)*($B$2:$B$5000>0))
/SUMPRODUCT((DATE(YEAR($C$2:$C$5000),MONTH($C$2:$C$5000),DAY(D$1))
=DATE(YEAR(D$1),MONTH(D$1),DAY(D$1)))*$B$2:$B$5000*
($A$2:$A$5000>0)*($B$2:$B$5000>0)))/100)

Each of these is still in one cell.
 
N

nca77

the first formula is working great.

the second formula works for column A, but when there is a negativ
number in column B it gives me a 0 value.
 
N

nca77

yes, 99% of the time the numbers in columns A2:B5000 will be positive.

when they are not positive, i need to exclude them.
 
D

Dave Peterson

It worked for me when I put some simple test data in the worksheet.

What's in D2?

Maybe you can adjust the range to only go to row 5.

Then adjust the data in A2:A5, D1, D2.

If you have trouble, post back with that limited data.
 
N

nca77

ok, lets say D2 = 4/30/2004

Lets say the values in C2:C7 all are 4/30/2004 also

let's put a value of 100 in cells B2:B7

and put a value of 4.00 in cells A2:A7

using this data we should get a value of -600 for the first formula an
4.00% for the second formula.. (this does work fine)

BUT, let's change the value in A2 from 4.00 to -4.00

the first formula should now give me -500, but it doesn't.. it stil
gives me -600..

i hope this makes better sense.

thanks again,

nic
 
N

nca77

just to be even clearer..

this formula works when there are negative values in column A

=SUMPRODUCT($B$2:$B$5000*($A$2:$A$5000>0)*($B$2:$B$5000>0))

and this works when there are negative values in column B

=SUMIF($B$2:$B$5000,">0")

i just need to combine them somehow.. so that when there is negativ
values in A or B.. their values are not summe
 
D

Dave Peterson

My bet is that your data isn't really numbers--it's text masquerading as
numbers.

Your first formula adjusted for me.

But one of your formulas, you do this:

=IF(D2=0,"0",(SUMPRODUCT(.....

That "0" isn't a number. It's text. If you do the same thing with other
values, it'll cause unexpected results (errors!) in further calculations.

Try putting:
=istext(a2)
or
=isnumber(a2)

and see what happens.

If your values are the results of a formula, then change the formula.

If your values are data typed in, this may convert the text to numbers:
select an empty cell
copy it
select your range
edit|paste special|check Add

Excel will convert the values to real numbers.

If your data was the result of copying and pasting from the web, you could have
some non-breaking spaces in the cell.

David McRitchie has a macro that can clean up this stuff:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

If you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
N

nca77

when i typed =istext(a2) - i got false

when i typed =isnumber(a2) - i got true

the only reason i put the =IF(D2=0,"0" ,(SUMPRODUCT
was to get rid of the #DIV/0! when there was no data matching the mont
and year.

so back to my original question, is there a way to fully exclude th
negative values if either A or B are negative?

thanks,

nic
 
D

Dave Peterson

Make sure you use those checking formulas (=istext() and =isnumber()) against
cells that are causing the trouble--not A1.

Your formulas worked for me. I'm out of suggestions.

Sorry,
 
P

Peo Sjoblom

How about select the whole range, press F5, special,
constants (if they are not derived from formulas),
uncheck everything but text and see if any cells get selected

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
D

Dave Peterson

Good way to check the whole range.

Thanks for jumping in!



Peo said:
How about select the whole range, press F5, special,
constants (if they are not derived from formulas),
uncheck everything but text and see if any cells get selected

--

For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
Click to expand...
 
Top