help - follow up

  • Thread starter Thread starter nca77
  • Start date Start date
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
 
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.)
 
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
 
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.
 
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.
 
yes, 99% of the time the numbers in columns A2:B5000 will be positive.

when they are not positive, i need to exclude them.
 
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.
 
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
 
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
 
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
 
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
 
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,
 
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
 
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...
 
Back
Top