Excel percentage formulas can help you every day through major and minor problems – from determining sales tax (and gratuities) to calculating increases and decreases. We will go through some examples below: convert fractions into percentages; deduct sales tax from totals; percentage of total; percentage increase or decrease; percentage of completion; and percentage ranking (percentile).
Convert fractions to percentages
Percentages are a portion (or fraction) of 100. The math to determine a percentage is dividing the numerator (the number on top of the fraction) by the denominator (the number at the bottom of the fraction), then multiply the answer by 1
1. Enter 10 fractions in column A (from A2 to A11). (Note: Excel automatically reduces fractions to their lowest terms, such as changing from 6/10 to 3/5.)
Because the Excel standard is decimal, you must mark the range and format it for Fractions. This is how:
2. Copy the fractions in column A to Column B.
3. Highlight that range and go to the Home tab . Select Percentage from the drop-down list in the field Number formats .
NOTE: You can also select Format cells from the button Formatting in the Cells group.
Total Back Tax
Some companies sell products that include sales tax and then refund the tax for their payments to the IRS. Calculate this by dividing the "sticker" price (or the total receipt) by 1.0 plus the VAT rate. For example, if you paid $ 50 for a lamp and the local sales tax rate is 9%, you divide $ 50 by 1.09. The actual sales price before sales tax is $ 45.87 and the sales tax is $ 4.13. To check your answer, add the two numbers together or multiply $ 45.87 by 9%.
It's fine to do this with one calculator, but if you get sales tax on your weekly or monthly product sales, you only have to enter the formula once and then copy it to your entire sales and inventory spreadsheet.
1. Enter a dozen products in column A (from A2 to A14).
2. Then enter the corresponding total receipt price (including VAT) in column B (from B2 to B14).
3. Enter different random VAT percentages in columns C2 through C14 (so you have a number of different numbers to play with). Enter a few decimal / fractional percentages, such as 4.75%, because most sales taxes are not integers.
4. Enter this two-step formula in cell D2: = SUM (B2 / (C2 + 1)) . The goal here is to convert the tax rate to the entire divisor (for example, 9% to 1.09) and then divide the total receipt price ($ 198.56) by the entire divisor (1.09) to the correct get the selling price (before taxes) of $ 182.17.
5. Copy the formula from D2 to D14.
6. In cell E2, subtract D2 from B2 to get the actual "backed up" sales tax (for the IRS): = SUM (B2-D2). Copy the formula from E2 down to E14.
7. To check your answers, enter this formula in F2 to F14: = SUM (D2 * C2) . If the columns E and F match, your details are correct.
percentage of totals
If you are self-employed are or have an office at your home, a method that the IRS uses to determine your deductions (for the office part of your rent, utilities, household maintenance costs, etc.) is to subtract the square meters of the office from the total square meters from the house. You can claim a percentage of those totals. The math for this starts with dividing the square meters of the office by the total square meters of the house, and then calculating the overhead based on that percentage.
1. At the top, enter the total square meters of your house in cell B2.
2. Enter the total number of square meters of your office in C2.
3. Enter this formula in cell D2: = SUM (C2 / B2 ) to determine the percentage of square feet of the office (in this case 25%).
4. Enter your overheads for home and office in column A (rent, electricity, etc.)
5. Enter the monthly cost of each item in column B.
6. Enter this formula in cells C5 to C12: = SUM (B5 * 12) . This gives you the annual totals.
7. Enter this formula in cells D5 to D12: = SUM (C5 * $ D $ 2) . The cell address D2 must be absolute. Use function key F4 to add the dollar signs that make the formula absolute, so each cell in column D is multiplied by D2.
8. Total number of columns B, C and D in row 13.
Now you can see how much you have spent on monthly and annual overhead costs for the entire house and only for the office. Cell D13 shows your total home office deduction ($ 5,088.60).
9. To calculate the percentage of the total overhead per item, enter this formula in E5 through E12: = SUM (B5 / $ B $ 13) . Use these percentages to determine if your monthly / annual overhead costs fall within normal business practices.
JD Sartain / IDG
Percentage of totals for home office deduction and overhead
Percentage of price increases or decreases
For most companies, especially in the retailers, owners and managers want to know the percentages of rise and fall for just about everything from sales to salaries. Use the following formulas to calculate the percentages of increase and decrease in your company.
Suppose you have created a workbook with a "Increase-Decrease" spreadsheet tab. Another spreadsheet tab with the name "SalesTax" contains data from the Retail Sales Price.
1. Enter a dozen product items in column A of Increase-Decrease (or simply copy the same items used in the spreadsheet from part A above).
2. Enter the quantities sold for each item in columns B and D.
3. Enter this formula in the "Jan Sales" column (C2 to C14): = SUM (SalesTax! D2 * "Increase-Decrease"! B2) . This formula tells Excel to increase / decrease the retail price in column D of the SalesTax spreadsheet by the quantities in column B of the spreadsheet where our cursor is currently located.
4. Enter this formula in the "Feb sales" column (E2 to E14): = SUM (SalesTax! D2 * "Increase-Decrease"! D2) .
5. Then enter this formula in F2: = SUM (E2-C2) / C2 .
The positive figures show the sales increase percentage between January and February, while the negative figures represent the percentage of the decline in sales.
Percentage of a task or project completed  Use instead of spending money on a project management software program, the following formulas to manage the planning and flow of each project with the completion rate at specified intervals.
1. In column A, enter the names for half a dozen projects (in progress).
2. Enter the start and end dates of each project in columns B and C.
3. To determine the completion of the project (so far), subtract the start date from the end date. Enter this formula in columns D2 to D7: = SUM (C2-B2) .
4. In column E, enter the number of days completed so far. This is the only column of data that you will ever change; for example, open this spreadsheet once a day (or week) and change the data in this column to get accurate conclusions in columns F and G (remaining days and completed percentage).
5. To get the number of days remaining in each project, enter this formula in columns F2 to F7: = SUM (D2-E2) . This number will change continuously based on the number data in column E (number of completed days).
6. And finally, enter this formula to complete the percentage of the task / project so far: = SUM (E2 / D2) .
Imagine having dozens of people applied for national parks and forestry jobs. From the CVs and # 39; s and the first interviews, they all seem to be equally qualified. However, they must meet some minimum qualifications that are not generally indicated on CVs, such as how to safely remove porcupines from the face and neck of a dog.
The solution: Give the candidates a skill test and use Excel & # 39; s PERCENTRANK.EXC function to determine the ranking percentage of each applicant. Enter 10 applicant names in cells A2: A11. Enter the skill points (scores) between 10 and 100 for each candidate in cells B2: B11 and give the range a name.
Select / highlight cells B2: B11. Select Formulas> Define Name> Define Name and enter the word Scores in the field Name . Click the arrow next to the field field Range and choose Sheet1 from the list to identify the range location. Note: the field refers to contains the cell addresses of the marked / selected range. If the range is incorrect, click the red arrow on the right and enter the correct range addresses or select the correct cells again, and then click OK .
NOTE: You must define the "array" range and give it a name for the formula to work.
Then enter the following formula in cell C2: = PERCENTRANK.EXC (Scores, B2.2) where Scores is equal to the range name, B2 is the first value in the range and 2 means display of two decimal places.
Copy and paste the formula down through cell C11. Note that the only part of the function that changes as you scroll through the list is the cell address (B2, B3, B4, etc.).
You can format the decimal numbers as a percentage for a better view. Highlight the range and select Percent in the Format Cells dialog box. Or Copy column C and choose Paste > Special > Values to replicate the text in column D. However, keep in mind that if you have one of the values in column B, you must repeat the Copy-Paste-Special-Values step.
To ensure that the values are always updated and current, enter the following formula in D2: = VALUE (C2) Then copy and paste through D11.
Note that three applicants have 22 points with a ranking percentage of 72 percent. This means that their scores are greater than or equal to 72 percent of all test scores. If you change the first score from 22 to 23, the ranking percentage jumps to 90 percent, because this score is now in the top 90th percentile of all test scores.