A gain is usually an increase from the previously recorded value. The ability to manipulate Excel's Arithmetic Power is one of the greatest skills needed to calculate the gain or loss. We can, therefore, calculate %gain using the following general mathematics formula:
%gain or loss= (Gain or loss/previous value) *100. To find gain, all we need to do is: Subtract the previous value from the latter one. We can easily calculate Excel's percentage gain or loss if these values are supplied.
Gain or Loss Procedure:
1. Have the totals (previous and later values) which will be used.
2. Add a column for % gain or %loss.
3. Perform Subtraction on the cells from both the initial and recent values.
Type in the following formula:
=(C2-B2)
This formula alone will be responsible for calculating the gain or loss. Complete the formula so that it becomes similar to:
=(C2-B2)/B2
4. Press the Percentage symbol to convert the decimals to actual percentages.
5. From the calculation results, a negative percentage will imply a percentage loss, while a positive percentage will indicate again.
Excel formula for percentage change between two percentages
To find the percentage change between two percentages, we use the following formula:
=Percentage change/ (first percentage+ second percentage)/2
The percentage change is usually the difference returned from the subtraction of the two percentages: I.e., Percent one-percentage two. We then find the averages of the two percentages to find the dividend to be used in calculating the % change of the two percentages. The results are then multiplied by 100.
Steps:
1. Launch Excel and navigate to your worksheet.
2. Identify the column or cells with the two percentages.
3. Outside those two cells, insert the following formula:
=(B2-C2)/(B2+C2)/2
4. Press Enter.
5. Save changes.
Excel formula for percentage change for a year over year
To find the percentage change year over year:
= (This year's value-last year's value)/last year's value*100
Procedure in Excel for Percentage Change:
1. Launch Excel. Navigate to your worksheet.
2. Add a column for percentage Change year over year.
3. Add a zero at the topmost cell of the column since it coincides with the Beginning year.
4. Type the following formula.
=(B3-B2)/B2
5. Press enter.
6. Highlight the column with decimals and press the percentage symbol under the Number group.
7. Drag from the second cell of the column to find the percentage change year over year.
Profit and Loss Percentage Formula from Cost price and Sell Price
For you to calculate percentage loss or gain, you will use the mathematical formula subtraction and percentage formatting from the Number Format ribbon.
Steps;
1. If you have the cost price in cell C5 and the selling price in cell D5, you can calculate the percentage gain or loss and return a value in cell E5. In this case, go to cell E5 and type the formula below;
=D5-C5
2. Tap the Enter button and you will get the difference between the two prices.
3. Right-click on the mouse and drag it down the column to generate results for the remaining cells using the Autofill feature. Alternatively, you can use the Fill Handle (+) icon.
The steps give profit and loss values for two corresponding prices. The positive value indicates profit and the negative value indicates a loss.
4. To get percentage loss or profit, you can create another column for percentage let’s say column F, and write the following formula in cell F5:
=E5/C5
5. Tap the Enter button and you will see the values in cell F5.
6. Use the Autofill feature to fill the rest of the cells down the column with decimal values.
Doing this will give a percentage loss or profit in decimal values. To get whole numbers, you can simply multiply each value by 100. However, with Excel, you don't need to go all this way since it allows you to convert them easily. To do this, highlight all the cells ranging from F5 to F9, go to the Home tab and click the percentage (%) sign.
Profit And Loss Percentage Formula with Conditional Formatting
You can also use Excel Conditional Formatting to calculate percentage profit or loss. In this method, however, you will need a ready column with profit and loss amount, such as column E, and another one with percentage values, such as column F.
1. Highlight the cell range from E5 to E9.
2. Go to the Conditional Formatting option under the Home tab and select the New Rule option.
3. You will note a new dialogue box will appear.
4. In the New Formatting Rule dialogue box, choose Format only cells that contain option. Go to the Edit the Rule Description checkbox and click on less than.
5. In the next checkbox, write 0 since you want to format all negative values that indicate a loss.
6. Continue to click on the Format option which opens another Format Cells dialogue box.
7. In the dialogue box, go to the header tab and click on Fill. Next, choose the color you wish to use.
8. Finish by clicking OK on both the dialogue box and New Formatting Rule box and you will see all the negative values (less than 0) formatted.
Profit And Loss Percentage Formula Along with IF Function
The method combines the IF function with the profit and loss percentage formula. If you want to calculate percentage profit and loss and return a value in cell E5, you can proceed with the following steps:
1. Go to cell E5 and type the formula below;
=IF(D5=C5,"No profit,No loss",IF(D5<C5,"Loss",IF(D5>C5,"Profit")))
From the formula, the IF function will return No profit No Loss when Cost Price is equal to Selling Price, Profit when Cost Price is less than Selling Price, and Loss when Cost Price is more than the Selling Price.
2. Tap the Enter button.
3. Use the Fill Handle (+) icon to drag down the formula and generate results for the remaining cells in the column.
4. To get the loss or profit amount and percentages, you can simply use the steps in the first method.