Back to Forms in Excel VBA archive index

Back to archive home

I'm an Excel rookie who has been thrown into this with no training or manual. I'm trying to calculate some percentages. I've had a look at both Percentile and Percentrank, and can't get them to do what I need.

I have columns of numbers, adding up to various amounts. I need to calculate the percentage each one of these numbers bears to the total of each column.

IE Column contains the numbers 3, 11, and 6. Total is

20. I therefore need to know the percentages that 3, 11 and 6 would be of 20.

Does anyone have any idea of what formula would be needed to extrapolate the required percentages? I hope its a fairly simple one, as I have a number of them to do on a monthly basis.

Sorry if this is simplistic, but I have exactly three days experience with Excel. Thank you.

FW

Check out our Excel Resources | ||||

Hi FW

Here is one easy way:

=A1/SUM(A1:A10) Then format the cell as a percentage.

This will tell you the what percentage A1 is of the Total of A1:A10

Dave

OzGrid Business Applications

FW, if you modify Dave's formula as follows:

=A1/SUM(A$1:A$10)

...you can copy it down a column adjacent to

your list of numbers and accurate results.

FW-

a brief overview of calculating percentages

a brief overview of calculating percentages

A1 3 =A1/SUM($A$1:$A$3)

A2 11 =A2/SUM($A$1:$A$3)

A3 6 =A3/SUM($A$1:$A$3)

OR

A1 3 =A1/$A$4

A2 11 =A2/$A$4

A3 6 =A3/$A$4

A4 20

then format the calculations as percentages.

The "$" creates an 'Absolute' reference that doesn't change as you copy the calculation

HTH

Mark

Thanks muchly, everyone. It now seems to be working.

FW

This archive is from the original message board at www.MrExcel.com.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.