I have arrays arranged down a column, one array for each product formula. I
have the data in Pounds per Batch with the Total Pounds at the bottom. I
want to convert the elements of the array (the ones above the total) to
percentages. I obviously can take element #1 * 100 / Sum but the problem is
some arrays have two elements, some have 5, some have 8...

Example:

What I have:
Element 1 300
Element 2 200
TOTAL 500

(next array)
Element 1 900
Element 2 1800
Element 3 450
Element 4 1850
TOTAL 5000

What I'd like to have Excel generate:
Element 1 300 60.00
Element 2 200 40.00
TOTAL 500 100.00 (this would be optional)

(next array)
Element 1 900 18.00
Element 2 1800 36.00
Element 3 450 9.00
Element 4 1850 37.00
TOTAL 5000 100.00 (once again, optional for the
total)

Thanks

Not sure about those optional totals. I think I would put them into another
column. It could probably be done in one formula in the same column but it
would be really long and really complicated.

This will get the percentages but not the optional totals:

Entered in C1 and copied down as needed:

=IF(OR(A1="",A1="total"),"",B1/INDEX(B1:B$9,MATCH("Total",A1:A$9,0)))

--
Biff
Microsoft Excel MVP


"Confused_in_Houston" wrote
in message ...
I have arrays arranged down a column, one array for each product formula.
I
have the data in Pounds per Batch with the Total Pounds at the bottom. I
want to convert the elements of the array (the ones above the total) to
percentages. I obviously can take element #1 * 100 / Sum but the problem
is
some arrays have two elements, some have 5, some have 8...

Example:

What I have:
Element 1 300
Element 2 200
TOTAL 500

(next array)
Element 1 900
Element 2 1800
Element 3 450
Element 4 1850
TOTAL 5000

What I'd like to have Excel generate:
Element 1 300 60.00
Element 2 200 40.00
TOTAL 500 100.00 (this would be optional)

(next array)
Element 1 900 18.00
Element 2 1800 36.00
Element 3 450 9.00
Element 4 1850 37.00
TOTAL 5000 100.00 (once again, optional for the
total)

Thanks

Use Excel 2007, PivotTable
with % of column option:
http://www.savefile.com/files/1816894