Over the years I’ve come across a few functions and tips that are worthwhile keeping in your back pocket. They won’t be needed all the time but can come in handy in certain situations!
Here are the tips and how I think about these functions. Whilst you can find the exact syntax in Excel help itself, sometimes it’s not very clear. So here are my tips for the day – in laymen’s terms!
Remove Spaces
If you want to remove unnecessary spaces in a sentence or word, use =TRIM(text/cell reference).
Weighted Average Calculation Tips
To calculate a weighted average (or a total), use the SUMPRODUCT() function.
In the example below, assume you have purchased 6 lots of shares at different prices and you want to work out what’s the average price you have paid.
One way to calculate it is in columns B and D, you can total the number of shares and calculate the total cost per lot of shares (i.e column B * column C). You can then divide the total cost by the total number of shares to calculate the average (cell C8).
Alternatively, a single formula approach can be the SUMPRODUCT shown in column H. This essentially does the following calculation in cell G2 “(B2 * C2) + (B3 * C3) + (B4 * C4) etc…”
Count The Number Of Positives (Or Negatives) In A Range
Use =COUNTIF(range,”>0″) if you want to calculate the number of positive values in a range. Replace “>0” with “<0” if you want to count the negatives.
Calculate Mortgage Payments
With central banks increasing interest rates to tackle inflation, you may want to calculate your new mortgage payment. Enter the PMT function. This will include the principal and interest component of your loan.
=PMT(interest rate, number of payments, loan balance) Note: ensure you use consistent periodicity for interest rate and the number of payments. Hence the divide by 12 on the interest rate in the example below.
Related article – How To Build A Mortgage Calculator In Excel
Wrap Text
Wrap text is a text editing function to ensure all text stays within a cell and doesn’t overflow.
At the top portion of the Excel sheet is a feature titled “Wrap Text”. The user can select that feature to wrap the text or shrink it to fit in the cell.
Format Cell Alignment
Another way to access the Wrap Tool function is within the “ab” button under the Home tab. By clicking the button, a drop-down menu will appear, from which you then select “Format Cell Alignment”. The drop-down menu will ask the user to choose Wrap Text, Shrink Text, or Merge Cells.
Hopefully, you’ve found a use for these functions and they save you some time!
You may also be interested in: