And one disadvantage if this method is that it would need an additional column for you to get the result (which is not such a big problem in most cases). In case you change the value in cell A2, the resulting data would automatically update. Note: Using the formula method has an advantage – which is that the result is dynamic. You will now get a whole column of cells containing the difference between cell A2 and cells B2:B11. Drag down the fill handle on the lower-right corner of cell C2 to copy the formula for all cells (C2:C11).Press the Return/Enter key on your keyboard.You can also manually add these dollar signs if you want. Notice that the cell location A2 changes to $A$2. Lock the cell location A2 by clicking either before, after, or in between the reference to A2 and pressing the F4 Key.Click on a cell of an empty column, say C2 and type the following formula in the formula bar: =A2-B2.The easiest way to do this is by using a simple subtraction formula.
Let us say you have a dataset as given below (cells B2:B11) and you want to subtract each of these values from the value in cell A2.
Subtract Multiple Cells from a Cell using a Formula Subtract Multiple Cells from a Cell using VBScript.Subtract Multiple Cells from a Cell using Paste Special.Difference between Absolute and Relative Cell References in Excel.Subtract Multiple Cells from a Cell using a Formula.While the resulting value is correct, it’s not in the right format that you want in the cell.Īnd this has a really easy fix – you just need to change the cell format. Sometimes you’ll notice that after you have subtracted a percentage value from a number, the resulting value isn’t in the proper format (sometimes it shows a percentage value instead of a number). Converting Percentage Back to General Values In case you do not want this to happen, you can convert the formula into values (i.e., remove the formula and only keep the values). Note that the result of this formula is dynamic, which means that in case you change the Sales value or the Commission percentage value, it would automatically update.
In the above formula, instead of calculating the percentage value that we need to multiply with the number, we use the value of the initial sale (in cell B2) and subtract the commission sales value (given by B2*C2) from it. You can also use the below formula: =B2-B2*C2 Since 100% is equal to 1, to deduct a specific percentage value from a number, we first calculate the deducted percentage value by subtracting the percentage value from 1, and then multiplying it with the number.
0 kommentar(er)
