Solved: calculate percentages using sumproduct?

[joeyc]

1.) I have one problem. I need to be able to automatically calculate the values in column D.

2.) Note, the number of products are undefined. I may have 3 products or I have 10. Also, the number of rows per product are undefined. I may have 25 or I may have 1,000.

3.) Let me explain what I did in column D. For every row of every product in column D I performed a calcuation. I need one specific calculation if a number in column C is positive and another calculation if the number in column C is negative. The range apart of the sumif function corresponds to the range of the product within this file. I have three products. The other two are down below.

4.) Here is an example. I start with D2. Is C2 positive or negative? Negative. So use the code below.


-C2/SUMIF($C$2:$C$390,"<0",$C$2:$C$390))

5.) Here is another example from cell D12. Is D12 positive or negative. Positive. So use the code below:


C12/SUMIF($C$2:$C$390,">0",$C$2:$C$390)

6.) By the way, I picked up a macro from someone on this board where the names of my product will be listed starting in K17 and going across. Maybe this can help in some way?

Thank you in advanced for any help. It all sounds weird but this would be very helpful. I am almost done here.

[Bob Phillips]

You do not clearly explain what you want to do. You say you want to automatically calculate, in what way does that differ from what you have?

[joeyc]

Hi,

Currently, I have formulas in column D that does this work. Most of the time, I have 10 - 20 products here. It is time consuming to write formulas with the appropriate range(s) then drag them down.

I would like something done here where this column is generated automatically. Once again, the range a product occupies is undefined. For example, the first product USMN takes up a few hundred rows while Mutual Fund A and Mutual Fund B takes up a few.

Does this help? Or should I elaborate on something else?

[Bob Phillips]

Try this array formula

=SIGN($C2)*$C2/SUM(IF($A$2:$A$1000=$A2,IF(C2<0,IF($C$2:$C$1000<0,$C$2:$C$1000),IF($C$2:$C$1000>0,$C$2:$C$1000))))

[joeyc]

It seems to work, but I need to modify it so that if a value in column c is blank an error message won't be returned.

Currently, I dragged this formula all the way down to 1,000.

[joeyc]

Wait let me try something...

[Bob Phillips]

It seems to work, but I need to modify it so that if a value in column c is blank an error message won't be returned.

Currently, I dragged this formula all the way down to 1,000.

=SIGN($C2)*$C2/SUM(IF($A$2:$A$1000=$A2,IF(ISNUMBER($C$2:$C$1000),IF(C2<0,IF($C$2:$C$1000<0,$C$2:$C$1000),IF($C$2:$C$1000>0,$C$2:$C$1000)))))

[joeyc]

Things are slow. I have samples as apart of this attached workbook. Sample 1 is typical. Sample 2 is the most strain that would be placed on this application. But even with Sample 1 just to clear the data or even paste it in takes a long time. I am still exploring another solution. :think:

However, your formula does work. I looked at it and found it to be quite elegant. Thank you very much.

[joeyc]

Actually, I managed to fix this. But thank you so much xld.

:beerchug: