How to Write Formulas in Excel for Percentage – 30+ Formula

In this article, you will learn how to write formulas in Excel for Percentage. A quick way to calculate percentages in Excel, find the basic formulas in Excel for Percentage and a few more formulas for calculating percentage increase, Percent of total and more.

There are several formulas in Excel for Percentage that can be used to calculate various types of Percent-based calculations. Here are some of the most commonly used formulas in Excel for Percentages.

What are the Formulas in Excel for Percentage

The most popular programme for both personal and professional use is Microsoft Excel. Most people are aware of Microsoft Excel and use it regularly to complete their tasks accurately. With the aid of formulae, we may quickly perform millions of calculations in MS Excel. It will help us save time and effort. A few Excel Formulas for the Percentage are provided below.

Also Read – Top 30 Apps Shortcut Refresh Key

How to Write Formulas in Excel for Percentage

The Microsoft formulas in Excel for Percentage can be written in various ways and it depending on conditions. Here are the top 30 popular Formulas in Excel for Percentages. All the formula begins with the “=” symbol without quotes else we will get errors.

The percentage of a certain value in proportion to the total is calculated using this formula. For instance, you can use the method below to determine what percentage each sale represents of the overall sales for the month if you have a column of sales data:

= (Value / Total) * 100

For example, if you want to find out what percentage of your sales came from a specific product, and your total sales for the year were $100,000, and sales for that product were $20,000, the formula would be:

= (20000 / 100000) * 100

This would give you the result of 20%, indicating that 20% of your sales came from that product.

The percentage difference between two values is determined using this formula. For instance, you may use the formula below to determine the percentage difference between two months when you have sales data for two different months.

= ((New Value - Old Value) / Old Value) * 100

Where “New Value” is the more recent sales data and “Old Value” is the earlier sales data.

For example, if sales in January were $10,000 and sales in February were $12,000, the formula would be:

= ((12000 – 10000) / 10000) * 100

And the result would be 20%, indicating that sales increased by 20% from January to February.

Difference Formulas in Excel for Percentage calculates the percentage difference between two values. For example, if you have two investment options and you want to know the percentage difference in return between the two options, you can use the following formula:

= (New Value - Old Value) / ((New Value + Old Value) / 2) * 100

Where “New Value” is the value of the more recent investment option and “Old Value” is the value of the earlier investment option.

For example, if the value of Option A was $100,000 and the value of Option B was $120,000, the formula would be:

= (120000 – 100000) / ((120000 + 100000) / 2) * 100

And the result would be 16.67%, indicating that Option B has a 16.67% higher return than Option A.

Also Read – 200+ Shortcut Key for MS Office for Beginner to Pro

The rank formula is very popular among the Formulas in Excel for percentages. The rank of a value is determined by this formula as a proportion of all values. For instance, you may use the method below to determine the percentage rank of a given student’s test score if you have a list of test results:

= (Rank / Total Number of Scores) * 100

Where “Rank” is the rank of the score in the list and “Total Number of Scores” is the total number of scores in the list.

For example, if the score of a student is ranked 10th out of 50 scores, the formula would be:

= (10 / 50) * 100

And the result would be 20%, indicating that the student’s score is in the top 20% of all scores.

To calculate the percentage markup on a product, use the following formula:

= ((Sale Price - Cost Price) / Cost Price) * 100

For example, if you bought a product for $50 and sold it for $75, the formula would be:

= (($75 – $50) / $50) * 100

This would give you the result of 50%, indicating that you have marked up the product by 50%.

To calculate the percentage discount on a product, use the following formula:

= (Discount / Original Price) * 100

For example, if a product originally costs $100 and is now on sale for $80, the formula would be:

= ((100 – 80) / 100) * 100

This would give you the result of 20%, indicating that the product is discounted by 20%.

To calculate the percentage difference between two numbers, use the following formula:

= (Difference / ((Number 1 + Number 2) / 2)) * 100

For example, if you want to find out the percentage difference between 80 and 120, the formula would be:

= ((120 – 80) / ((80 + 120) / 2)) * 100

This would give you the result of 33.33%, indicating that there is a 33.33% difference between 80 and 120.

To calculate the percentage contribution of a value to a total, use the following formula:

= (Value / Total Value) * 100

For example, if you want to find out what percentage of the total revenue was generated by a specific product, and the total revenue was $100,000, and the revenue from that product was $20,000, the formula would be:

= (20000 / 100000) * 100

This would give you the result of 20%, indicating that the product contributed 20% to the total revenue.

To calculate the percentage of a goal achieved, use the following formula:

= (Actual Value / Target Value) * 100

For example, if your sales target for the month was $50,000 and you achieved $40,000, the formula would be:

= (40000 / 50000) * 100

This would give you the result of 80%, indicating that you have achieved 80% of your sales target.

To calculate the percentage deviation from a target value, use the following formula:

= ((Actual Value - Target Value) / Target Value) * 100

For example, if your target profit was $10,000 and you only achieved $8,000, the formula would be:

= (($8,000 – $10,000) / $10,000) * 100

This would give you the result of -20%, indicating that you fell short of your target by 20%.

To calculate a weighted average, use the following formula:

= (Sum of (Value * Weight)) / Sum of Weight

For example, if you want to calculate the weighted average of grades for a student based on their different weightings, the formula would be:

= (Sum of (Grade * Weight)) / Sum of Weight

This would give you the weighted average based on the different weightings assigned to each grade.

To calculate the compound interest on a loan or investment, use the following formula:

= P*(1+r/n)^(n*t)

Where P is the principal amount, r is the annual interest rate, n is the number of times the interest is compounded per year, and t is the time period.

For example, if you invest $5,000 at an annual interest rate of 6%, compounded monthly for 5 years, the formula would be:

= 5000*(1+0.06/12)^(12*5)

This would give you the result of $6,639.72, indicating the amount your investment will grow to after 5 years.

Also Read – Formula Based Salary Slip Generator Template

To calculate the simple interest on a loan or investment, use the following formula:

= (P * r * t) / 100

Where P is the principal amount, r is the annual interest rate, and t is the time period.

For example, if you borrow $10,000 at an annual interest rate of 8% for 3 years, the formula would be:

= (10000 * 8 * 3) / 100

This would give you the result of $2,400, indicating the amount of interest you will pay over the 3-year period.

To calculate the gross profit margin of a business, use the following formula:

= ((Revenue - Cost of Goods Sold) / Revenue) * 100

For example, if your business generates $100,000 in revenue and your cost of goods sold is $50,000, the formula would be:

= (($100,000 – $50,000) / $100,000) * 100

This would give you the result of 50%, indicating the gross profit margin of your business.

To calculate the net profit margin of a business, use the following formula:

= (Net Profit / Revenue) * 100

For example, if your business generates $100,000 in revenue and your net profit is $20,000, the formula would be:

= ($20,000 / $100,000) * 100

This would give you the result of 20%, indicating the net profit margin of your business.

To calculate the debt-to-equity ratio of a business, use the following formula:

= Total Liabilities / Total Equity

For example, if your business has $100,000 in total liabilities and $50,000 in total equity, the formula would be:

= $100,000 / $50,000

This would give you a result of 2, indicating that your business has a debt-to-equity ratio of 2.

To calculate the ROI of an investment, use the following formula:

= ((Current Value - Initial Value) / Initial Value) * 100

For example, if you invested $10,000 in a stock and it is now worth $15,000, the formula would be:

= (($15,000 – $10,000) / $10,000) * 100

This would give you the result of 50%, indicating the ROI of your investment.

To calculate the break-even point of a business, use the following formula:

= Fixed Costs / (Price per Unit - Variable Costs per Unit)

For example, if your business has fixed costs of $50,000, sells a product for $10 per unit, and has variable costs of $6 per unit, the formula would be:

= $50,000 / ($10 – $6)

This would give you the result of 12,500 units, indicating the number of units your business needs to sell to break even.

To calculate the contribution margin of a business, use the following formula:

= Price per Unit - Variable Costs per Unit

For example, if your business sells a product for $10 per unit and has variable costs of $6 per unit, the formula would be:

= $10 – $6

This would give you the result of $4 per unit, indicating the contribution margin of your product.

To calculate the price elasticity of demand for a product, use the following formula:

= ((Q2 - Q1) / ((Q1 + Q2) / 2)) / ((P2 - P1) / ((P1 + P2) / 2))

Where Q1 and Q2 are the initial and final quantities demanded, and P1 and P2 are the initial and final prices.

For example, if the initial quantity demanded of a product was 100 units at a price of $10 per unit, and the final quantity demanded was 80 units at a price of $12 per unit, the formula would be:

= ((80 – 100) / ((100 + 80) / 2)) / (($12 – $10) / (($10 + $12) / 2))

This would give you the result of -1.67, indicating that the product has a relatively elastic demand.

Also Read – SBI Credit Card Reward Points & Redeemption Process

To calculate the covariance between two sets of data, use the following formula:

= SUMPRODUCT((X-XBAR),(Y-YBAR))/COUNT(X)

Where X and Y are the sets of data, XBAR is the mean of X, and YBAR is the mean of Y.

For example, if you have two sets of data X = {10, 15, 20, 25, 30} and Y = {20, 25, 30, 35, 40}, the formula would be:

= SUMPRODUCT((X-AVERAGE(X)),(Y-AVERAGE(Y)))/COUNT(X)

This would give you the result of 62.5, indicating the covariance between the two sets of data.

To calculate the correlation coefficient between two sets of data, use the following formula:

= COVAR(X,Y) / (STDEV(X) * STDEV(Y))

Where X and Y are the sets of data.

For example, if you have two sets of data X = {10, 15, 20, 25, 30} and Y = {20, 25, 30, 35, 40}, the formula would be:

= COVAR(X,Y) / (STDEV(X) * STDEV(Y))

This would give you the result of 1, indicating a perfect positive correlation between the two sets of data.

To calculate the net present value (NPV) of an investment, use the following formula:

= SUM(CF/(1+r)^t)

Where CF is the cash flow for each period, r is the discount rate, and t is the time period.

For example, if you have an investment that generates cash flows of $10,000, $15,000, and $20,000 in years 1, 2, and 3 respectively, with a discount rate of 5%, the formula would be:

= ($10,000/(1+0.05)^1) + ($15,000/(1+0.05)^2) + ($20,000/(1+0.05)^3)

This would give you the result of $40,338.91, indicating the NPV of the investment.

Calculate the internal rate of return using the below formulas in Excel for Percentage in IRR

= IRR(values)

Where values are the cash flows for each period, with negative values indicating cash outflows and positive values indicating cash inflows.

For example, if you have an investment that generates cash flows of -$10,000, $5,000, $7,000, $8,000, and $10,000 in years 0, 1, 2, 3, and 4 respectively, the formula would be:

= IRR(-10000,5000,7000,8000,10000)

This would give you a result of 14.79%, indicating the internal rate of return on the investment.

The Office formulas in Excel for Percentage are helpful to calculate data easily and fastly. To calculate the beta coefficient of a stock, use the following formula:

= COVAR(Stock Returns, Market Returns) / VAR(Market Returns)

Where Stock Returns are the returns of the stock and Market Returns are the returns of the market.

For example, if you want to calculate the beta coefficient of a stock that has returned 10%, 15%, 5%, and 8% over the past four years, and the market has returned 8%, 10%, 12%, and 15% over the same period, the formula would be:

= COVAR({0.1, 0.15, 0.05, 0.08}, {0.08, 0.1, 0.12, 0.15}) / VAR({0.08, 0.1, 0.12, 0.15})

This would give you the result of 1.06, indicating that the stock is more volatile than the market.

This formula calculates the percentage increase or decreases between two values. For example, if you have two prices for a product and you want to know the percentage increase or decrease in price, you can use the following formulas:

For increase:

= ((New Value - Old Value) / Old Value) * 100

Where “New Value” is the higher price and “Old Value” is the lower price.

For example, if the original price was $10 and the new price is $15, the formula would be:

= ((15 – 10) / 10) * 100

And the result would be 50%, indicating that the price has increased by 50%.

For decrease:

= ((Old Value - New Value) / Old Value) * 100

Where “Old Value” is the higher price and “New Value” is the lower price.

For example, if the original price was $20 and the new price is $16, the formula would be:

= ((20 – 16) / 20) * 100

And the result would be 20%, indicating that the price has decreased by 20%.

The difference from mean formulas in Excel for Percentage calculates the percentage difference between a value and the mean of a data set. For example, if you have a list of test scores and you want to know how much a particular score differs from the average score, you can use the following formula:

= ((Value - AVERAGE(range)) / AVERAGE(range)) * 100

Where “Value” is the individual score you want to calculate the percentage difference for, and “range” is the data range.

For example, if you have a list of test scores in cells A1 through A20 and you want to know the percentage difference between a score of 85 and the average score, the formula would be:

= ((85 – AVERAGE(A1:A20)) / AVERAGE(A1:A20)) * 100

And the result would be the percentage difference between 85 and the average score.

The percentage change in a moving average over a certain period is calculated using this formula. For instance, you can use the method below to determine the % change in the 3-month moving average over time if you have a list of sales data:

= ((3-Month MA - 3-Month MA Previous Period) / 3-Month MA Previous Period) * 100

Where “3-Month MA” is the 3-month moving average for the current period, and “3-Month MA Previous Period” is the 3-month moving average for the previous period.

For example, if the 3-month moving average for the current period is $50,000 and the 3-month moving average for the previous period is $40,000, the formula would be:

= ((50000 – 40000) / 40000) * 100

And the result would be 25%, indicating that the 3-month moving average has increased by 25% over the previous period.

In Excel, the percentage change in a number from a base year to a given year is calculated using the change from base year formulas. For instance, you can use the method below to determine how much sales have climbed or decreased from a base year if you have sales data for numerous years.

= ((Value - Base Year Value) / Base Year Value) * 100

Where “Value” is the value for the given year and “Base Year Value” is the value for the base year.

For example, if the sales in 2019 were $50,000 and the sales in 2020 were $60,000, and 2019 is the base year, the formula would be:

= ((60000 – 50000) / 50000) * 100

And the result would be 20%, indicating that the sales increased by 20% from the base year.

% error is calculated using the Excel error calculations for %. This determines the percentage difference between the estimated and real values. For instance, you may use the method below to determine the percentage error between your estimate and the actual sales for a specific month if you have predicted the sales for that month.

= ((Actual - Estimated) / Actual) * 100

Where “Actual” is the actual value and “Estimated” is the estimated value.

For example, if your estimated sales for a particular month were $50,000 and the actual sales were $60,000, the formula would be:

= ((60000 – 50000) / 60000) * 100

And the result would be 16.67%, indicating that your estimate was off by 16.67%.

Summary

Above we have explained the basic formulas in Excel for Percentage calculations in daily life. You can practice using the given formula and if facing any difficulty kindly write to us.

Frequency Asked Questions

Hope you enjoy this information. Please tell us your thoughts and suggestions about this article. Keep reading more SEO tips & how-to guides and do follow and subscribe to our social media platforms for more latest updates.