There are three ways that we can calculate price increases. The first calculates the increase & then adds this to the original value; the second calculates the new price only (though, of course, we can use that & the original price to work out the difference). Both methods presume that there’s a standard price uplift across all products.
If different increases apply to different products then we need to apply those individually, ‘though, of course, if they’re product group based, then we can use a VLOOKUP function to reduce the calculations.
Method #1: Calculating the increase and adding to original price.
So, we’ll start with our basic spreadsheet with some values already in place. As I’ve been playing my guitars more, recently, we’ll use those as the products…
The first thing to do is to populate C2:C4with the calculation to work out the increase.
To do this, we select the range C2:C4 & then type in the formula that we need to calculate the price increase…
You’ll notice that the cell references change colour to match the shaded area of the relevant cells. Also, having entered the G1 reference, pressing the F4 Function key puts $ signs to the left of both the column letter and row number. This makes the reference absolute & allows us to copy the formula down secure in the knowledge that we will always be referring to cell G1. We could, of course, have typed these in as we input the formula.
Having typed our formula CTRL + ENTER inputs it into cells C2:C4 (the area that we had already selected).
The final step is to select D2:D4 and input an appropriate formula to add the values in columns B & C together to show the new price. We can either use a SUM function (e.g. SUM(B2@C2) ) or simply add the two values together; as the latter method needs fewer keystrokes we’ll use that.
Again, CTRL + ENTER and our spreadsheet is complete.
Method #2: Calculating the final price directly.
With this method, we start with the same information, however, this time we will calculate the new price directly.
So, this time, we select cells D2:D4 and enter our formula.
As we’re wanting to work out the final price, on this occasion, rather than simply multiplying the original price by the increase, we multiply by the value of “1 plus the increase”; in the above case, therefore that becomes by 1.25. Again, F4 key applies the “$” signs and CTRL + ENTER to actually input the formulas.
Also note that the 1+$G$1 is enclosed in brackets. In part, this helps show the calculation more clearly, but most importantly it avoids Excel using it’s natural order of calculation which follows the rules of BODMAS to determine calculation order (Brackets – Overs – Divide – Multiply – Add – Subtract) which would end up (for row 2) multiplying 600 by 1 & then adding 0.25 to the result – not quite what we had in mind. For more information on BODMAS, check out this tip: https://us4b.co.uk/bodmas-important-understand-excel/
Finally, we can use a simple subtraction to work out the increase.
So, our final spreadsheet looks the same as Method 1, even though the values have been calculated in a different way.
Method #3: Product Group Increases
Both of the above methods are based on the fact that the same price increase is being applied “right across the board”. There are two other possibilities…
- Different price increases per product – unfortunately, there’s no real way round this other than calculating them individually,
- Different price increases per product group – this provides us with the opportunity to get Excel to do most of the work for us, though we’ll need to use a VLOOKUP Function to help us.
So, first of all, we need to add some additional information… the product groups and the increase per group… (for those of you interested “Single Coil” and “Humbucker” are the types of pickups that the guitars have. I’ve inserted a new column for the category, so all of the other columns have simply moved one to the right.
We can use either Method 1 or Method 2 above as the basic formula to work out the increase, however, rather than a reference to $G$1 in those, we will need to use our VLOOKUP Function to provide us with the correct %age increase.
So, we’ll use Method 1 as there are less elements in the formula.
The syntax (or Excel gobbledygook) for the VLOOKUP is: =VLOOKUP (value, table, col_index, [range_lookup])
Alternatively, in somewhat plainer English: =VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE)
Putting this into practice…
So, the final formula (in D2) now reads: =C2*VLOOKUP(B2,$F$2:$G$3,2,FALSE) The $ signs for the top left and bottom right cells of the range ensure that when the formula is copied down, the range stays the same.
We can then create our simple calculation to add this increase to the original price to see our new price.
CTRL + ENTER again finishes our spreadsheet off for us
I’ll cover the VLOOKUP Function in more detail in a future tip.
Method #4: A “quick and dirty” solution
The basic premise of all of the above methods is that the price increase might change. If, however, a price increase is imposed upon you as a “fait accompli”, then there’s a “quick and dirty” method that you could use.
If we go back to the first example & presume that we’ve been told that all products will have prices increased by 25%, then rather than showing a %age Increase of 25% we can, instead, create a “multiplier” of 1.25
We then use a lesser-known feature of Copy and Paste to copy the multiplier and then use PasteSpecial to multiply the values in the price column by it…
to select Paste Special
From the Paste Special options select Multiply, then click OK
The new & updated values (NOT formulas) are now displayed
