MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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

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

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.

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

Posted by FW on March 28, 2001 9:32 AM

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

FW