Excel percentage formulas can get you through problems large and small every day—from determining sales tax (and tips) to calculating increases and decreases. We’ll walk through several examples below: turning fractions to percentages; backing sales tax out of totals; percentage of total; percentage increase or decrease; and percentage of completion.

Turning fractions to percentages

Percentages are a portion (or fraction) of 100. The math to determine 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 like this: 6 divided by 12 (which equals .5) times 100 equals 50 percent.

In Excel, you don’t need a formula to convert a fraction to a percent—just a Format change. For example:

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

Because the Excel default is decimal, you’ll need to highlight the range and format it for Fractions. Here’s 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 dropdown list in the Number Formats field.

JD Sartain / PC World

How Excel converts fractions to percentages

Note: You can also select Format Cells from the Format button in the Cells group.

Back sales tax out of totals

Some companies sell products with the sales tax included, then just back the tax out for their payments to the IRS. Calculate this by dividing the “sticker” price (or receipt total) by 1.0 plus the sales tax rate. For example, if you paid $50 for a lamp and the local sales tax rate is 9%, divide $50 by 1.09. The actual retail price before sales tax is $45.87, and the sales tax is $4.13. To check your answer, just add the two numbers together, or multiply $45.87 by 9%.

Using a calculator to do this for just one item is fine, but if you’re pulling sales taxes out of your weekly or monthly product sales, you only have to enter the formula once, then copy it throughout your entire sales and inventory spreadsheet.

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

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

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

4. Enter this two-step formula in cell D2: =SUM(B2/(C2+1)). The object here is to convert the tax percentage to the whole number divisor (e.g., 9% to 1.09), and then divide the receipt total price ($198.56) by the whole 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” sales taxes (for the IRS): =SUM(B2-D2). Copy the formula from E2 down through E14.


Source link

If you are looking to make money from running your own business at home, visit the links below.

Computers and Software Buyers Guide

Compare Computers and Laptops

Mobile Phones Buyers Guide

Compare Mobile Phones