Tidy up a formula!

Posted by Richard Larocque on January 09, 2002 8:36 PM

I really don't have a problem,, because I have the results I'm looking for, however I'm wondering if I can improve on the following:

I have a large database with 9500 rows. In column A, I have the following:
a
a
a
b
b
b
c
c
d

In column B, I have numerical values.

In column C I wrote the formula:
=IF(A8=A9,"",SUMIF($A$8:$A$9500,A8,$B$8:$B$9500))

In column D I wrote the formula:
=IF(C8="","",PERCENTRANK($C$8:$C$9500,C8))

In column E I have the following:
=SUMIF($A$8:$A$9601,A8,$D$8:$D$9500)

The result that is desired is finally achieved in column E.

Now instead of using three columns to get the desired result, can it be done in one combined formula?

Thanks

Posted by Qroozn on January 09, 2002 8:54 PM

i can't see the relation between the cells?

you can always replace cell references in a formula with other formulas

how do they all relate?

Posted by Richard Larocque on January 09, 2002 9:01 PM

Column C is the results I get from A and B.
Column D works on column C and E works on E.

Column A has various industries.
Column B has price changes.
Column C sums up the price changes as industries change.
Column D percentranks the results from column C.
Column E sumifs the results from column D.

Does this help?

Posted by Aron on January 09, 2002 9:08 PM

Another idea: If this is for your own analysis, use the Autofilter with custom filters to get percents and stuff. Or Auto Subtotal. Otherwise, the PivotTable/Chart feature would make this a snap. Check out the 25 uses for a PivotTable links at http://search.office.microsoft.com/assistance/product.aspx?p=Excel

Have fun...

Posted by Richard Larocque on January 09, 2002 9:15 PM

IndustryPrice
a 9 100.00%
a 7 100.00%
a 5 21 100.00% 100.00%
b 3 0.00%
b 1 0.00%
b 2 6 0.00% 0.00%
c 4 66.60%
c 6 10 66.60% 66.60%
d 8 8 33.30% 33.30%

Does this help? Results in last column is what is required.