Free Resources

Templates & Downloads | White Papers & eBooks | Blog Articles | Test your Excel Knowledge | Newsletter

Featured Blog Articles

 3 Reasons Why INDEX MATCH Is Better Than VLOOKUP

3 Reasons Why INDEX MATCH Is Better Than VLOOKUP

By Wyn Hopkins VLOOKUP has met it's match! Here are 3 reasons why you should use INDEX/MATCH instead of VLOOKUP:
  1.  You can “lookup to the left”
  2.  You don’t get incorrect results when a column is inserted or deleted from your data
  3.  When used in conjunction with Tables, the formulae are a lot more meaningful.

=INDEX(SelectColumnContainingWhatYouWantToReturn, MATCH(TheSingleCellYouAreLookingUp,ColumnWhereYouAreLooking,0),0)      

And here are the steps for easy implementation:
  1. Copy this formula then go into Excel / File>Options>Proofing>Autocorrect
  2. In the replace box type iii, then in the with box paste the formula
  3. Whenever you need a better alternative to vlookup just type iii (autocorrect kicks in), and then double click on each piece of text to select the appropriate column/cell
Alternatively, download our free Excel Ribbon (The Great 48 toolbar) which includes a button o put this formula in for you automatically.
Read More...

 3 Ways To Calculate Time

3 Ways to Calculate Time

By Jonathan Lim About time Let's say it is 8:00:00 am now, and I want Excel to calculate the time 1 hour, 1 minute and 1 second earlier or later. There are three methods by which this can be done:

1.  The Long Way

Using a constant of the hour, minute and second
  • An hour is approximately 1 / 24 of a mean solar day;
  • A minute would be = 1 / (24 x 60)
  • A second would be = 1 / (24 x 3600)
The following range names have been created for this exercise:

HrsNum        Cell C45 HrUnit           Cell D45 MinsNum      Cell C46 MinuteUnit   Cell D46 SecondUnit   Cell D47 SecsNum       Cell C47

Cells C45 to C47 are hardcoded inputs that the user can tweak.  In this example, the default inputs are 1 hour, 1 minute and 1 second. Based on this, the formula would look like this:

= B51 - (( HrsNum * HrUnit) + ( MinsNum * MinuteUnit) + ( SecsNum * SecondUnit))

2.  Using the SUMPRODUCT function

The SUMPRODUCT function multiplies the ranges in columns C and D and returns the sum of the results that would be subtracted from our start time of 8:00:00 am. The formula would look like this:

= B51 - SUMPRODUCT( $C$45:$C$47, $D$45:$D$47)

3.  Using the TIME function

In Excel, the TIME function returns the decimal number of a particular time, which can be added to/subtracted from to the timing stated above. The syntax for this function is:

TIME(hour, minute, second), where hour is a number from 0 to 23; minute is a number from 0 to 59; and second is a number from 0 to 59

Using the TIME function, the formula would look like this:

= B51-TIME( HrsNum, MinsNum, SecsNum)

Read More...

 3 Ways To Do More With Less In Excel

3 Ways to Do More with Less in Excel

By Jeff Robson   Productivity Improvement - Flat Series

Every Business Today is Being Asked to do more with less

... fewer people, shorter timeframes, less capital and resources. Improved productivity is a basic necessity. To really achieve breakthroughs in efficiency and productivity, we all have to work smarter by using technology better. In our experience, the biggest opportunities for improved productivity are in the areas of:
  1. Data manipulation: converting raw data into useful information
  2. Management reporting: analysing and presenting data
  3. ‘What if’ analysis: financial modelling and forecasting
“Spend time analysing information, rather than preparing and collating it.” Let's break it down ...

1. Data Manipulation

This is a huge source of inefficiency, errors and problems. Data is frequently either exported or manually keyed into Excel, then manually cleaned, reformatted and summarised in some way.  Errors are frequent because the processes are so manual: formulas aren't copied, data is re-keyed, and cells are manually linked/re-linked every time the process is run. These issues are incredibly common in businesses because staff don't realise there are better ways; they don't know that Excel could do most/all the work for them; and they just follow the process they did last month. This wastes staff time, which costs the business. It's even more costly if management make a bad decision that is based on inaccurate data.

2. Management Reporting

There is an inordinate amount of staff time spent collating, preparing and constantly updating data for reports. Management often don't realise just how much work goes into making a beautiful report that gives them all the information they need to make a decision. Yet, there so many ways to speed up these processes:
  • Automation through VBA macros so processes can run just by clicking a button
  • Linking Excel directly to databases so data, charts and reports can all be refreshed in seconds (or less!)
  • Using the power of Excel's formulas to automatically do the work for you, rather than doing lots of manual amendments every month.
Many people don't realise the power of Excel to make their lives easier by saving vast amounts of time, effort and frustration! It's quite common to reduce the time staff spend on a given process by 90-95% with just a few simple changes in Excel.

3. Scenario planning and ‘what-if’ analysis

Many businesses find it hard to answer the question "What would happen to profit/cash flow/NPV if we change X?" X could be prices, input costs, salaries, contractors, suppliers, order quantities, premises, a major project ... Being able to answer these questions is often impossible (since many businesses don't have a solid financial model), or very difficult and time-consuming (since many other businesses have a financial model that's hard to use). And, what if we also ask “What happens to profit/cash flow/NPV if we’re wrong about X?”. This requires robust sensitivity analysis of key drivers so is imperative to informed decision-making. Making decisions in the absence of data and proper analysis is a recipe for disaster as you're flying blind. Well-structured financial models that are easy to use and understand allow decisions to be made with confidence. These are all the kinds of things we help businesses with, every single day of the year. If you'd like to find out how we can help your business do much more with less, contact Jeff Robson on +61 8 6210 8500 or by email for a confidential discussion today.
Read More...

 3D Sum Formula

3D Sum formula

Is it possible to easily add up the same cell across spreadsheets?

By Neale Blackwood

Yes, these are called 3D formulae. To create a 3D sum formula that sums all the A4 cells in multiple sheets, open a new workbook, click the AutoSum toolbar icon, then click the Sheet2 tab, then click the A4 cell, then hold the Shift key down and click the Sheet3 tab, release the Shift key and press Enter.

The formula will look like this:

=SUM(Sheet2:Sheet3!A4)

You can also select ranges using this method. The formula can be typed, but the mouse method is better. Be wary of using this method if you move sheets around the workbook. In the above example, if Sheet2 was moved to the right of Sheet3, the formula will change to exclude Sheet2.

The above formula can be read as sum of all the A4 cells between Sheet2 on the left and Sheet3 on the right. In other words, moving sheets around can have an impact on 3D formulae. The sheet number makes no difference to the formulae, it is their relative position that decides which sheets are included in the calculation.

One 3D technique uses the two sheet names First and Last. Then the sheets to be included are moved to the right of First and the left of Last. The formula would look like this:

=SUM(First:Last!A4)

Read More...

 3D SUMIF Formula

3D SUMIF formula

Is it possible to do a 3D SUMIF? By that I mean to SUM a column of values based on the codes in another column through multiply sheets?

By Neale Blackwood

Yes, but the formula is complicated. Assume three sheets named, Jul, Aug and Sep. In a fourth sheet you want to do a SUMIF in all these sheets to calculate a total for the first quarter. The sheets all have the same layout. Column A contains the code you want to match to, and column B contains the value you want to SUM based on the code in Column A. Cell A4 contains the code to which you'd like to match. Here's the formula, and note it uses the curly brackets {} of an array formula within the formula itself. This is not an array formula; it is entered normally. The formula for a 3D SUMIF is:

=SUM(SUMIF(INDIRECT({"jul","aug","sep"}&"!A:A"),A4, INDIRECT({"jul","aug","sep"}&"!B:B")))

To include more sheets, just insert more names separated by commas within both sets of the curly brackets {}. The references "!A:A" and "!B:B" refer to full columns. I did this to shorten the formula and make it easier to read. Referring to full columns is inefficient and would slow the calculation time down if used extensively. It is more efficient to use actual ranges such as "!A1:A1000" and "!B1:B1000". If your sheet name are numbered, it may simplify the formula slightly. For example, if the sheets are named M1, M2 and M3 the formula would be:

=SUM(SUMIF(INDIRECT("M"&{1,2,3}&"!A:A"),A5, INDIRECT("M"&{1,2,3}&"!B:B")))

The INDIRECT function allows you to build cell references from text. The curly brackets work as an array and allow you to create multiple references. The SUMIF function works as per normal using the three separate references created by the INDIRECT functions. The SUM function adds up the three SUMIFs. If you didn't have the SUM function, the result is a SUMIF on the first sheet in the series only.

 

Read More...