Excel percentage formulas: 6 common uses - fitefesionfluen

Surpass portion formulas can get you through problems large and small every day—from determining nuisance tax (and tips) to calculative increases and decreases. We'll walk through several examples below: turning fractions to percentages; backing sales tax out of totals; percentage of sum up; percentage step-up or decrease; percentage of completion; and pct higher-ranking (percentile).

How to turn fractions into percentages

Percentages are a portion (or divide) of 100. The mathematics to fix a percentage is to divide the numerator (the number on top of the fraction) by the denominator (the number on the bottom of the fraction), then multiply the answer by 100. For example, the fraction 6/12 turns into a decimal the like this: 6 divided by 12 (which equals 0.5) multiplication 100 equals 50 per centum.

In Stand out, you don't need a formula to convert a fraction to a percent—just a Format change. E.g.:

1. Enter 10 fractions in column A (from A2 through A11). (Note: Surpass automatically reduces fractions to their lowest footing, such equally changing 6/10 to 3/5.)

Because the Excel default is decimal, you'll need to highlight the range and data format IT for Fractions. Here's how:

2. Copy the fractions in column A to Column B.

3. Highlight that rove and attend the Home base tab. Select Percentage from the dropdown list in the Number Formats field.

JD Sartain / IDG

How Excel converts fractions to percentages

Banker's bill: You can also superior Formatting Cells from the Format button in the Cells group.

How to back sales tax out of totals

Some companies deal products with the sales tax enclosed, then just back the tax out for their payments to the IRS. Calculate this away dividing the "sticker" price (or receipt total) by 1.0 positive the sales tax grade. For example, if you paying $50 for a lamp and the local sales tax plac is 9%, divide $50 by 1.09. The actual retail Mary Leontyne Pric before sales tax is $45.87, and the nuisance tax is $4.13. To check your answer, just impart the deuce numbers racket together, or multiply $45.87 by 9%.

Using a reckoner to act up this for just one item is fine, but if you'atomic number 75 pulling gross revenue taxes unsuccessful of your weekly or monthly product sales, you simply have to go in the formula one time, then copy it throughout your smooth sales and inventory spreadsheet.

1. Enter a cardinal or so products in column A (from A2 through and through A14).

2. Next, recruit the similar receipt total price (tax included) in column B (from B2 through B14).

3. In columns C2 through C14, enter several whimsical nuisance tax percentages (so you have some different numbers to play with). Be sure to enter some decimal/incomplete percentages so much as 4.75%, because most sales taxes are not whole numbers.

4. Embark this two-step formula in cell D2: =SUM(B2/(C2+1)) . The object here is to change the task percentage to the whole number factor (e.g., 9% to 1.09), and past divide the acknowledge total price ($198.56) by the undiversified number divisor (1.09) to get the correct retail price (before taxes) of $182.17.

5. Copy the formula from D2 down to D14.

6. In cell E2, subtract D2 from B2 to get the actual "backed-out" gross sales taxes (for the IRS): =SUM(B2-D2). Copy the formula from E2 down through E14.

7. To double-check your answers, enter this formula in F2 through F14: =SUM(D2*C2). If the columns E and F catch, your data is correct.

JD Sartain / IDG

Back out the nuisance tax from the receipt's total

How to get percentage of totals

If you'Ra self-employed operating room have an office in your home, indefinite method the Internal Revenue Service uses to determine your deductions (for the office portion of your rent out, utilities, household maintenance costs, etc.) is to subtract the square footage of the office from the home's total square footage. You can claim a percentage of those totals. The math for this one begins with dividing the office square up footage by the interior's total square footage, so calculating the elevated settled thereon percentage.

1. Across the top, enter upon your home's summate square footage in cellphone B2.

2. Enter the tally direct footage of your spot in C2.

3. Enter this formula in cell D2: =SUM(C2/B2) to determine the office's percent of square feet (in this case, 25%).

4. Introduce your home and office overhead items in column A (rent, electricity, etc.)

5. Enter the monthly be of each item in editorial B.

6. Enter this pattern in cells C5 through C12: =SUM(B5*12) . This gives you the yearly totals.

7. Enter this formula in cells D5 through D12: =SUM(C5*$D$2). The cell address D2 must be absolute. Use function key F4 to add the dollar mark signs that make the chemical formula inviolable, thusly each cell in column D is multiplied past D2.

8. Add together columns B, C, and D along row 13.

Now you can see how much you spent on monthly and annually overhead for the entire house and for the office only. Cell D13 shows your total home government agency deductive reasoning ($5,088.60).

9. To calculate the percent of the total overhead by item, enter this formula in E5 through E12: =SUM(B5/$B$13). Utilise these percentages to determine if your unit of time/yearly overhead is within sane business practices.

JD Sartain / IDG

Percentage of totals for home office tax deduction and overhead

How to lick part of monetary value growth Beaver State decrease

For most businesses, especially in retail, owners and managers like to do it the percentages of increase and decrease for just about everything, from gross revenue to salaries. Use the following formulas to calculate the percentages of step-up and decrease in your ship's company.

Imagine you've created a workbook with a spreadsheet tab called "Increase-Decrease." Another spreadsheet tabloid known as "SalesTax" includes Retail Sales Price data.

1. Enter a dozen or so product items in column A of Increase-Decrease (OR just copy the same items used in the spreadsheet from part A above).

2. Insert the quantities oversubscribed of each item in columns B and D.

3. Enter this formula in the "Jan Sales" column (C2 through and through C14): =SUM(SalesTax!D2*'Increase-Lessen'!B2). This formula tells Excel to multiply the Retail Sales Terms in column D of the spreadsheet called SalesTax by the quantity amounts in column B of the spreadsheet where our pointer currently resides, Increase-Decrease.

4. Enter this formula in the "Feb Sales" column (E2 through E14): =SUM(SalesTax!D2*'Increment-Decrease'!D2).

5. Next, enter this convention in F2: =Amount(E2-C2)/C2.

The supportive numbers show the gross sales increase percentage between January and February, while the negative Book of Numbers represent the percentage of diminution in sales.

JD Sartain / IDG

Percentages of increase and/Oregon decrease in product sales by month

How to calculate percentage of a task operating theater project completion

Instead of pocket money on a project management software computer program, use the following formulas to manage the planning and flow of from each one project with the percentage of completion at specified intervals.

1. In column A, enter the names for sextuplet projects (in progress).

2. In columns B and C, enter the Start and Remnant Dates of each project.

3. To determine the propose completion (so far), take off the Start Date from the End Date. Enter this formula in column D2 through with D7: =SUM(C2-B2).

4. In column E, enter the number of days completed until now. This is the only chromatography column of data that you will ever change; for example, one time a day (operating room week), accession this spreadsheet and alter the data therein column to get accurate conclusions in columns F and G (years left and percentage completed).

5. To get the number of days left in each externalize, enter this formula in column F2 through with F7: =Totality(D2-E2). This Book of Numbers bequeath continually change founded on the number data in column E (keep down of days realized).

6. And final, enter this formula to get the percentage of the task/project consummated, yet: =SUM(E2/D2).

JD Sartain / IDG

Calculate the per centum of a task/project complete

How to do per centum ranking

Conceive of that you have dozens of mass applying for national parks and forestry jobs. From the resumes and the initial interviews, they all seem to be equally qualified. However, they moldiness meet several minimum qualifications that are non in the main indicated on resumes, such as how to safely take porcupine quills from a dog's face and neck.

The solution: Hold the applicants a skills trial, and use Excel's PERCENTRANK.EXC function to determine the ranking percentile of each applicant.

Enter 10 applier names in cells A2:A11. Enter the acquisition points (scores) 'tween 10 and 100 for each applier in cells B2:B11 and then name the range.

Quality/highlight cells B2:B11. Select Formulas > Define Figure > Define Name and enter the word Scores in the Name field box. Click the arrow beside the Scope landing field box and choose Sheet1 from the list to identify the range location. Observance the Refers To field of operation box contains the cell addresses of the highlighted/selected lay out. If the tramp is incorrect, click the bolshie arrow on the right and enter the correct range addresses, or Re-select the correct cells, and and so come home OK.

NOTE: You must define and name the range of the "array" for the formula to work.

Next, embark the following formula in cell C2: =PERCENTRANK.EXC(Scores,B2,2) where Rafts equals the range name, B2 is the opening value in the range, and 2 substance exhibit ii decimal places.

Re-create and paste the formula down through prison cell C11. Notice that the only piece of the function that changes as you cursor low-spirited direct list is the cellphone speech (B2, B3, B4, etc.).

You can format the denary numbers as a percent for easier viewing. Just highlight the range and select Pct from the Format Cells dialog. Or Copy column C and  chooseGlue > Special > Values to replicate the text in newspaper column D. Note, still, that if you change whatsoever of the values in editorial B, you will have to repeat the Simulate-Paste-Special-Values step.

To ensure the values are e'er updated and current, enter the following formula in D2: =VALUE(C2), then copy and paste down through D11.

Notice that ternary applicants accept 22 points with a ranking centile of 72 percent. This means that their scores are greater than or up to 72 percent of all the test scores. If you change the first score from 22 to 23, the ranking portion jumps to 90 percent, because this nock is instantly in the top 90th percentile of all the tryout scores.

JD Sartain / IDG

Consumption the PERCENTRANK.EXC go to determine the ranking percentiles of a elite group

Source: https://www.pcworld.com/article/412211/excel-percentage-formulas.html

Posted by: fitefesionfluen.blogspot.com

Share this post