Home » Our Blog » calculations

# How to calculate GST at 15% using Excel formulas

I have been asked twice in one week how to write Excel formulas to calculate GST at the rate of 15%.

You can find information on how to calculate the rate on the NZ Inland Revenue website. I’ve taken this a step further and put together a tutorial on how to put these calculations into Excel formulas. Read more

# Excel – How can I do a VLOOKUP based on more than one value?

There are times when you want to return a match for a VLOOKUP based on two values, not one.  For example, we may have customers who have multiple branches.  Each branch is recorded under the same customer number.  So how do we look up info for a specific branch if all of the branches are using the same customer number?

# Excel – removing unwanted spaces from a cell

PROBLEM: Wasting time removing unwanted spaces in Excel data.

SOLUTION: Get to know the Trim function and save yourself time (and headaches).

# Excel – The relative to absolute shortcut key

PROBLEM: wasting time manually changing cell references in Excel from relative (e.g. A3) to absolute (e.g. \$A\$3).

SOLUTION: learn the relative to absolute shortcut key!

# Excel – Find the Average, Count and Sum of a range without having to write a formula

PROBLEM: wasting time writing formulas to find the
Average, Count and Sum of a range.

SOLUTION: learn how in Excel you can quickly calculate the
Average, Count and Sum of a range without having to write a formula.

# Excel – Super quick way to copy formulas

PROBLEM: Having to click and drag…and drag….and drag…to copy a formula down a large number of rows.

SOLUTION: Start using the AutoFill secret shortcut.  You’ll love it!

# Excel – Hiding errors using the IFERROR function

PROBLEM: Dealing with formulas that result in error messages.  For example the #DIV/0 error or the #N/A error.

SOLUTION: Start using the IFERROR function  Read more

# Excel – How to summarize data from multiple worksheets (Part 1 of 2)

I recently ran an Advanced Excel course where I was asked “what is the quickest way to pull data from multiple worksheets into a summary worksheet?” Excel offers several solutions for consolidating and summarizing data from different worksheets and workbooks. Read more

# Excel – Paste Values shortcut in Excel

Turning formulas into values is a very common procedure to most users of Excel.  Paste Values allows you to replace a formula with the result.  For example, a cell containing the formula “=3*2” can copied and using Paste Values the formula result, i.e. “6” will be pasted into the cell, replacing the formula.

Here’s a fabulous (and very popular) tip to save you time when you paste values in Excel. Read more

# Excel – How do I move the negative sign in front of the number?

Anyone that regularly imports data into Excel will be familiar with the process of “data cleaning”. This is where you need to remove spaces and irrelevant data in order to get the data fit for use in Excel.

I recently had an excellent question regarding working with data imported into Excel where negative numbers are imported with the negative sign to the right of the number, e.g. 500-. These numbers are commonly referred to as “mirrored negatives”. Read more