Fast Hyperlinks
Excel’s PERCENTOF perform returns the proportion {that a} subset of knowledge contributes to a complete dataset, saving you from having to create extra or complicated formulation to generate the identical end result.
The PERCENTOF Syntax
The PERCENTOF perform has two arguments:
=PERCENTOF(a,b)
the place
- a (required) is the information subset that makes up a part of the entire dataset, and
- b (additionally required) is the entire dataset.
In different phrases, the PERCENTOF perform tells you the worth of subset a as a proportion of the entire dataset b.
Utilizing PERCENTOF With a Single Worth
Probably the most easy use of the PERCENTOF perform is to calculate the proportion of a single worth towards an total whole.
On this instance, as an instance you’ve got been requested to investigate the June efficiency of 14 outlets in England. Particularly, your process is to work out every store’s contribution to the general gross sales whole for that month.
In my instance, I’ve used a formatted Excel table and structured references. Consequently, my formulation are simpler to parse, and if I add extra information to row 16 and under, the brand new values will robotically be included within the calculations. For those who do not use tables and structured references, you could want to regulate your formulation in order that they include absolute references, and you will have to replace your formulation should you add extra rows of knowledge.
To do that, in cell E2, sort:
=PERCENTOF(
Then, click on cell D2, which is the information subset whose contribution to the general whole you wish to calculate. For those who’ve used a formatted desk, this can drive Excel so as to add the column identify to your method, and the @ image implies that every row can be thought of individually inside the consequence. Following this, add a comma:
=PERCENTOF([@[June sales]],
Lastly, choose all the information in column D—together with the subset you chose within the earlier step however excluding the column header—to inform Excel which cells make up the entire dataset. It will seem in your method because the column identify in sq. parentheses. Then, shut the unique, rounded parentheses.
=PERCENTOF([@[June sales]],[June sales])
If you press Enter, the consequence will show as a collection of zeros. However don’t be concerned—this occurs as a result of the information within the proportion column is at present represented as decimals, moderately than percentages.
To alter these values from decimals to percentages, choose all of the affected cells, and click on the “%” icon within the Quantity group of the Dwelling tab on the ribbon. Whilst you’re there, click on the “Improve Decimal” button so as to add decimal locations, which is able to enable you to differentiate between comparable values.
And there you’ve gotten it! A formatted Excel desk containing a column that exhibits every store’s contribution to the general gross sales figures.
Associated
Excel’s 12 Number Format Options and How They Affect Your Data
Alter your cells’ quantity codecs to match their information sort.
Utilizing PERCENTOF With GROUPBY
Whereas PERCENTOF can be utilized by itself to point out particular person proportion contributions, it was primarily added to Excel to be used with different capabilities. Particularly, PERCENTOF works rather well with GROUPBY to additional break your information down into specified classes and return the output as percentages.
Sticking with the store gross sales information within the instance above, as an instance your intention is to seek out out which counties are attracting the best proportion of gross sales.
As a result of GROUPBY creates a dynamic array, the perform will not work in a formatted Excel desk. Because of this it is advisable to create your information retrieval desk in an space of your spreadsheet that is not formatted as a desk.
To attain this, in cell G2, sort:
=GROUPBY(Gross sales[County],Gross sales[June sales],PERCENTOF)
the place
- Gross sales[County] is the sector within the unique Gross sales desk that may decide the grouping,
- Gross sales[June sales] is the sector containing the values that may generate the chances, and
- PERCENTOF is the perform that may flip the uncooked information into comparative values.
The GROUPBY perform additionally lets you enter one other 5 optionally available fields, similar to discipline headers, kind order, and filter array, however I’ve left these out on this instance to point out you how one can use it with PERCENTOF in its easiest type.
If you press Enter, Excel will group the gross sales by county (in alphabetical order by default), presenting the information in decimalized type.
To show these decimals into percentages, choose the figures, and click on the “%” icon within the Quantity group of the Dwelling tab on the ribbon. You may as well regulate the variety of decimal locations within the proportion figures by clicking the “Improve Decimal” or “Lower Decimal” buttons in the identical group of that tab.
Now, even when your information adjustments dramatically, the GROUPBY perform will decide up these adjustments and regulate the categorization desk accordingly.
You do not have to cease there along with your PERCENTOF enjoyable! For instance, you’ll be able to embed this perform inside PIVOTBY to interrupt your information down additional utilizing multiple variable, earlier than displaying the output as percentages.