Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

Calculating Percentages?

Posted by FW on March 27, 2001 1:26 PM
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

Re: Calculating Percentages?

Posted by Dave Hawley on March 27, 2001 1:33 PM

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


Re: Calculating Percentages?

Posted by Mark W. on March 27, 2001 1:37 PM
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.


Re: Calculating Percentages?

Posted by mseyf on March 27, 2001 1:47 PM
FW-
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


Re: Calculating Percentages?

Posted by FW on March 28, 2001 9:32 AM
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.