Sum Data in Col B, Add % of Total Col C, Add Rolling % of Total Co

G

Guest

Another challenge for a noob like me:

I start out with this, with the headers at Row 6:

Security Value
Apples 10
Oranges 45
Dates 5
Pears 20
Weebles 1
Wobbles 1
MMA 1
MMB 3
MMC 1


Security Value % of Total % Rolling
Apples 10 11.5% 11.5%
Oranges 45 51.7% 63.2%
Dates 5 5.7% 69.0%
Pears 20 23.0% 92.0%
Other 2 2.2% 94.3%
MM 5 5.7% 100.0%

Total 87

I tried to do this on my own but failed. Here is what is tricky about this
request:

1) Preference to insert formulas rather than to calculate and insert actual
values -- I may have to add or delete data once the macro is one and leaving
formulas in will provide me with that required flexibility.

2) Add the sum of Column B total at the bottom.

3) There are a few cells that I have to add together and consolidate into
one cell (MMA, MMB, MMC) and labled "MM." This resulting summed figure
should be hard-coded.

These cells actually go by different names and sometimes there are
differently labeled cells that I have to consolidate. Ideally I can edit the
macro to lookup cells that contain certain words (e.g., MMA, MMD, MMZ, MMF,
MMJ) and consolidate into one cell and with one label "MM".

4) Values less than 2% of the total value should be consolidated into one
figure and labled "Other."

This is a tough one. A macro I attempted to createy got derailed after
summing column B (I obviously didn't get too far).

Thanks very much for generously offering your time...
 
B

Bob Phillips

Running total

=SUMIF($A$6:$A$15,$A20,$B$6:$B$15)/SUM($B$6:$B$15)

Asusming that is in B20, in C20

=SUM($B$20:B20)

and copy down.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
G

Guest

Thanks Bob. But even I know how to do that. My question is, is it possible
to do all of this stuff, en total, with a macro? Thanks very much.
 
Top