From time to time I get asked the question “what order do formulas evaluate?”

The acronym BEDMAS can help you remember. It stands for:

Brackets: Any operation(s) contained in brackets will be carried out first followed by any exponents.

Exponents: Then any exponents like ^ or SQRT

Division or Multiplication (left to right): Excel considers these to be of equal importance, and carries out these operations in the order they occur from left to right in the equation.

Addition or Subtraction: The same goes for addition and subtraction. They are considered equal in the order of operations. Whichever one appears first in an equation, either addition or subtraction, is the operation carried out first.

Ok, so what if you know all that (after all you probably learnt BEDMAS at school way-back-when) but you’re still stuck because your formula isn’t returning the result you want.

Well, thankfully Excel has a tool for that too.

Evaluate Formula Tool

The Evaluate Formula tool allows us to see how each component of a formula evaluates, one step at a time.

Let’s take the VLOOKUP formula we looked at last week as an example:

How to use the Evaluate Formula Tool

1. Select the cell containing the formula you want to evaluate. Ours is in G6.
2. On the Formulas tab of the Ribbon in the Formula Auditing group select Evaluate Formula.
    
   

   The Evaluate Formula dialog box will open:

   
    

3. Click the ‘Evaluate’ button to view the value of the underlined reference. In the example above the underlined reference is cell D6, and you can see below it evaluates to ‘William’.

    

   

4. If the underlined reference is part of another formula you can use the ‘Step In’ button to display the other formula. Then ‘Step Out’ to go back and continue evaluating.

    

   

5. In the image below you can see all but the last step of the formula evaluated:

    

   

6. And finally the result:

    

   

The Evaluate Formula tool is especially useful for nested formulas that may not be returning the correct result.

I also like to use it to find why I’m getting # errors or checking that the result is calculating as I expect.

But be warned; it can’t work miracles. If you can’t get Excel to even accept your formula then you may need to consult your office Guru first

I had an email from Bobcat today asking how to lookup data that is spread across multiple columns.

We want to find the Employee number from Table 1 and put it in column G of Table 2.

The solution is quite simple. We just need to join together the cells containing the name in Table 2, before looking them up in Table 1.

We can do this within a VLOOKUP formula like this:

=VLOOKUP(D6&” “&E6&” “&F6,table_1,2,FALSE)

Note:  table_1 in the above formula is the named range for cells A6:B11

See in the formula bar how I’ve joined the text from columns D, E and F together using the ampersand symbol. I’ve also added a space between the text by inserting double quotes with a space between.

The VLOOKUP formula is resolving the D6&” “&E6&” “&F6 arguments of the formula like this:

=VLOOKUP(William J Oxley,table_1,2,FALSE)

Thanks for your question Bobcat.

Using Data Validation to restrict what gets entered in a cell or range of cells is great for standardising your workbooks.

But what if you want a second data validation list to only show values that are specific to the first list, like the one below?

Well, that’s exactly what Jackie emailed me about the other day, and here’s how you do it.

How to set up Dependent Data Validation Lists

First of all enter the data for your lists. These are mine:

Now, give your primary list a named range.

To insert a Named Range:

1. Highlight the range of cells containing your list, excluding the header.
2. Up in the name box (the name box is highlighted by the orange box in the image below) type in the name you want to use (with no spaces) and press ENTER. As you can see, mine is called dv_country.

Now give your secondary lists named ranges too.

Here’s the trick: you must use the data from your primary list for your secondary list names.

So, my secondary list for the USA states is called ‘usa’, Australia’s secondary list is called ‘australia’ and the UK list is called ‘uk’ as you can see in the image below.

Now you’re ready to set up your data validation.

Setup Data Validation

1. Choose the cells you want validated using your first list. Mine are A4:A6.
2. On the Data tab of the ribbon > Data Validation > Data Validation
3. Choose ‘List’ from the ‘Allow’ field
4. Enter the named range for your primary list in the Source field
5. Press OK

Setup Dependent Data Validation

1. Select the cells you want validated. Mine are B4:B6.
2. On the Data tab of the ribbon > Data Validation > Data Validation
3. Choose ‘List’ from the ‘Allow’ field
4. In the source field enter an INDIRECT formula that references the first cell containing your primary data validation. Mine is A4 therefore my formula is =INDIRECT(A4)
5. Press OK

Bob’s your Uncle (as we used to say when I was about 12).

Download the workbook.

More on Named Ranges.

More on the INDIRECT Function.

What if my side eBay bike business sold an extra 10%, what if I increased my price by $15 per bike, what if my costs increased by $5000 per year?

What-if analysis is commonly done in Excel by saving different versions of the same workbook, or having a different sheet for each scenario which then become difficult to compare.

But did you know you could do it all in the one workbook, on the one sheet and select from a list of scenarios and see your data automatically update, then create a summary comparison of each scenario?

Well, it’s true, unlike my eBay side business which is complete fabrication solely for the purpose of this tutorial

Excel Scenario Example

Let’s say for the time being that my eBay bike story is true and I’m in the process of preparing my 5 year forecast.

Here are my assumptions that I’ve used in various calculations in the above forecast.

But I’m wondering what the effect would be if my growth was higher, or my price per bike was higher, or my margin was higher.

I can use the Scenario Manager to save different sets of variables (in my case my assumptions will be the variables), and then toggle between them to see how they affect my budget.

I’ve set up 4 different scenarios using the Scenario Manager (all based on how the different levels of profit will affect my lifestyle…keep reading and you’ll see what I mean).

How to Use Excel Scenarios

The Excel Scenario Manager is on the Data tab of the ribbon under What-if Analysis.

When you click the Scenario Manager the dialog box will open:

You can see I already have 4 scenarios (Still Hungry, Occasional Takeaway, Fine Dining and Happy Days). To add a new Scenario press the ‘Add’ button.

The Add Scenario dialog box opens.

1. Give your Scenario a name.
2. Select the cells that you want to alter. To select non-adjacent cells press CTRL+left mouse button.
3. Add a comment if you want and press OK

Now you can enter your Scenario values.


Note: if you want to keep your original scenario, set this up first by entering your values here. Then set up additional scenarios.

Once you’ve set up more than one scenario you can toggle between them by going back into the Scenario Manager and pressing the ‘Show’ button.

You can also create a summary of all scenarios by pressing the Summary button in the Scenario Manager.

You can then choose from a Scenario Summary or a PivotTable report.

The plain vanilla version of the Scenario summary looks like this:

And the Scenario PivotTable report looks like this:

Neither is great but with a little chocolate topping (read ‘improvement’) they can be better:

So now you don’t have to save a separate file or create a new sheet for each scenario, your workbooks are likely to be easier to manage and update, and less prone to errors.

Happy Days

Let’s say that a baby learning to crawl is an analogy for learning the AVERAGE function in Excel.

Then learning the AVERAGEIF function is like learning to walk, and learning the AVERAGEIFS function is like learning to run.

So, lace up your shoes and get ready to run

We’ll be using the table below in this tutorial. Inspired by my 5 year olds current obsession with the first Harry Potter movie.

Baby steps first:

Excel AVERAGE Function

=AVERAGE(your_data_range)

=AVERAGE(D4:D15)

=$271.58

As you can see, the Average function is fairly straight forward in that it simply averages a range of cells.

But there are some things you should know about how it works:

If one of the cells is blank it doesn’t include it in the number to average.

For example, there are 12 cells in our range D4:D15. So the AVERAGE function is actually summing the range of cells ($3,259), and then dividing them by 12 to get an average of $271.58.

But if cell D5 was blank it would sum the range of cells (and get $3,084) and divide them by 11 to get $280.36.

On the other hand, if cell D5 contained a zero it would still divide the sum by 12.

Excel AVERAGEIF Function

What if we wanted to get the average sales if the salesperson was Hermione?

That’s where the AVERAGEIF function comes into play. It allows you to average data in one range of cells where the data in another range matches a certain criteria.

The AVERAGEIF syntax is a bit different:

=AVERAGEIF(range, criteria, [average_range])

Where ‘range’ is the range containing your criteria, and [average_range] is the range of cells containing the values you want to average.

Let’s use the data below to find the average sales for Hermione.

Our formula would be:

=AVERAGEIF(A4:A15,”Hermione”,D4:D15)

=$317

In English;

=AVERAGE(referring to the range A4:A15, find Hermione, and average the values in the range D4:D15)

Note: the ranges of data must be the same size. In this example both refer to rows 4 to 15.

However, it wouldn’t work if one referred to rows 4 to 10 and the other referred to rows 4 to 15.

The limitation of the AVERAGEIF function is that you can only use one criterion.

AVERAGEIFS Function Syntax

Whereas if you wanted to find the AVERAGE sales by ‘Harry’ of the product ‘Time Turner’ in the ‘Hogwarts’ region you’d need to use the AVERAGEIFS function.

=AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2,…)

Using our example data again our AVERAGEIFS function would be:

=AVERAGEIFS(D4:D15,A4:A15,”Harry”,B4:B15, “Time Turner”,C4:C15,”Hogwarts”)

=$167

Two rows match our criteria:

Download the Workbook

Download the sample file and take a look at some ways you can enhance your AVERAGEIF/AVERAGEIFS formulas using named ranges and data validation lists.

Enhancement 1: Named Ranges

Notice in the formula bar how the first and last arguments of the syntax is ‘Sales_Person’ and ‘Price’ rather than a the cell ranges A4:A15 for Sales_Person and D4:D15 for Price?

This is called a named range and they make building your formulas quick and also easy to interpret later on.

Enhancement 2: Data Validation

In the AVERAGEIFS function I’ve also used named ranges. Plus I’ve used a data validation list or drop down list as they’re sometimes known as seen in action in the animation above.

The formula in cell G16 is:

=AVERAGEIFS(Price,Sales_Person,G11,Tool,G12,Region,G13)

This allows me to choose the criteria from the data validation lists in cells G11, G12 and G13 and the AVERAGEIFS formula will dynamically update to show the results for the new criteria.

Want to learn more tricks like this?

It’s techniques like this that I teach in my dashboard course to create reports that are interactive for the report recipient.

These features make your colleagues love you, because when you give them reports like this they are in control of getting the information they need quickly and easily.

And they save you time because you don’t have to create myriad of reports to cover every scenario.

It’s a win, win.

Click here to learn more about Excel Dashboard reports and how you can build interactive features into them.

Last week I had a question from Dan that I’d never come across before.

Dan has a dress shop with over 2000 dresses and he wanted a formula that would round prices up (in whole dollars) if it ended between 5 and 9, or down if it ended between 0 and 4.

For example: $143 would round down to $139, and $147 would round up to $149.

Now, the problem is that the ROUNDUP and ROUNDDOWN functions will do one or the other, but in this case we first need to establish which function to use.

Here’s my solution:

Here’s how it works in English:

=IF(the last value in cell A6 is less than 5, round down to the nearest 10, minus 1, otherwise round up to the nearest 10, minus 1)

Note: this won’t work if there are decimal places in the ‘Amount’ as the formula looks for the last value in the cell. So you either have to start with whole numbers or ROUND them to no decimal places.

RIGHT Function: RIGHT(A6,1) – Finds the last character in column A.

VALUE Function – The VALUE function is wrapped around the RIGHT function, and converts a text string that represents a number, to a number. This is necessary because sometimes the RIGHT function used on its own throws out the wrong result….I don’t know why, but it’s probably something to do with text vs numbers, but why it works sometimes and not others is unknown to me.

IF Function – The IF function then instructs Excel to choose either the ROUNDDOWN or ROUNDUP function based on whether the right most value in the ‘Amount’ column is greater than, or less than 5.

Why Minus 1 – Well, the -1 in =ROUNDUP(147,-1)  will give you $150, but we want $149 so we need to minus 1 from the rounded result; =ROUNDUP(147,-1)-1. Likewise for ROUNDDOWN.

As with most things in Excel there are many ways to skin a cat. How would you approach this? Please share your ideas in the comments below.

My Toughest Excel Challenge So Far!

Recently a member contacted me with a VLOOKUP question, but when I further understood the requirements it became a challenge I’d never come across before.

This pushed me to my Excel limits and I have to be honest…I nearly gave up twice! By giving up I mean I was going to settle for a work-around solution instead.

The Challenge

Christy wanted to find the last value in a row for a specific part number using a table of data in Sheet1 like this:

Notice how some rows don’t have a value in every cell? This is what makes this challenge unique.

You see Christy wanted to find the value for say, part A10106, for the month of June, but if June didn’t have a value then find the value for the previous month, and if May didn’t have a value then go to the previous month and so on.

And it wasn’t just one part, it was over 2000 parts and for every month of the year.

Not being one to shy away from a challenge I persisted, and this is my formula (Note: The table above was on Sheet1 and my formula is on Sheet2):

=IFERROR(INDEX(INDIRECT(“Sheet1!”&ADDRESS(MATCH(A4, Sheet1!$A:$A,0),1,1)&”:”&ADDRESS(MATCH(A4, Sheet1!$A:$A,0),MATCH(B3, Sheet1!$1:$1,0),1)),MATCH(9.99999999999999E+307,INDIRECT(“Sheet1!” &ADDRESS(MATCH(A4, Sheet1!$A:$A,0),1,1)&”:”&ADDRESS(MATCH(A4, Sheet1!$A:$A,0),MATCH(B3, Sheet1!$1:$1,0),1)))),0)

Phew. I’m not ashamed to say that I cheered out loud when I cracked this one.

Find Last Value in a Range

The first part of the challenge was to find the last value in each row.

We can use an INDEX and MATCH formula like this to find the last value in row 2:

=INDEX(Sheet1!A2:M2,MATCH(9.99999999999999E+307,Sheet1!A2:M2))

=52

But I need to find the last value for each month and for that I need the range A2:M2 to change for each month I look up, and for each part.

So that for Part Number A10106 for the month of June I look up the range Sheet1!A2:G2 and for the month of August I look up Sheet1!A2:I2, and so on.

To do this I need to replace the range Sheet1!A2:M2 with some functions that will dynamically update when I change the month and the Part Number, and for this I used INDIRECT, MATCH and ADDRESS.

Dynamic Range using INDIRECT, MATCH and ADDRESS

So my formula went from this:

=INDEX(Sheet1!A2:M2,MATCH(9.99999999999999E+307,Sheet1!A2:M2))

To this by replacing the ranges Sheet1!A2:M2 with the sections in blue:

=IFERROR(INDEX(INDIRECT(“Sheet1!”&ADDRESS(MATCH(A4, Sheet1!$A:$A,0),1,1)&”:”&ADDRESS(MATCH(A4, Sheet1!$A:$A,0),MATCH(B3, Sheet1!$1:$1,0),1)),MATCH(9.99999999999999E+307,INDIRECT(“Sheet1!” &ADDRESS(MATCH(A4, Sheet1!$A:$A,0),1,1)&”:”&ADDRESS(MATCH(A4, Sheet1!$A:$A,0),MATCH(B3, Sheet1!$1:$1,0),1)))),0)

Note: cell A4 contains the part number and B3 contains the month I want to look up.

The INDIRECT function returns a reference specified by a text string.

In this case the text string is generated using the MATCH function and I’ve used the ampersand to join components of the text string together.

The ADDRESS function obtains the address of a cell or range of cells. For example, ADDRESS(1,2) returns $A$2.

IFERROR – The Icing on the Top

And finally, if Excel couldn’t find the part number in the table I used the IFERROR function to enter a zero.

Download the Workbook

If you would like a more in-depth understanding of this formula why not download the workbook and reverse engineer the formula, or use the Evaluate Formula tool to step through the formula and watch as each part evaluates.

You’ll find the evaluate tool on the Formulas tab of the Ribbon in the Formula Auditing group.

Feedback

I’d love to know if you have a simpler way to tackle this challenge. Please post your solution below in the comments for us all to share.

Excel’s INDIRECT Function has many applications and perhaps its simplest is to fix a range of cells you want to reference.

For example a standard SUM function looks like this:

=SUM(B16:B24)

If you insert a row within this range the formula dynamically updates and becomes:

=SUM(B16:B25)

It will dynamically update even if you absolute the reference like this:

=SUM($B$16:$B$24)

To stop the reference dynamically updating when you insert rows or columns use the INDIRECT function like this:

=SUM(INDIRECT(“B16:B24”))

In most cases you’d want your formulas to dynamically update when you insert new rows or columns but there are some instances when this is a nuisance and that’s when INDIRECT is your ally.

How Does The INDIRECT Function Work?

Excel’s INDIRECT function returns a reference specified by a text string.

The text string can be obtained by:

• referencing another cell, or
• you can enter it in double quotes, or
• you can generate it by nesting other functions

And you can also use the ampersand (&) symbol to concatenate text and build your text string that way. More on using the ampersand in a moment.

Notice how the range specified in this INDIRECT function below is surrounded by double quotes, thus making it a text string.

INDIRECT Function Syntax

The syntax is: =INDIRECT(ref_text,[a1])

ref_text – is the text string (like in the example above “$B$16:$B$24”) or the cell you’re referring to containing the text string.

a1 – this is asking you if your reference uses the A1 reference style or R1C1 style. To use the A1 style* simply omit this argument, but if you want to use R1C1 enter ‘FALSE’.

*A1 reference style is what you’re probably familiar with and it is where the columns have letters and the rows have numbers. The R1C1 style uses the R to specify a row and then the number of that row, and the C to specify a column and then the number of that column.

In the example below the INDIRECT function in cell A4 is evaluating the contents in cell C4. I could have also typed this formula like this:

=INDIRECT(“C4”) and my result would again be ‘This text’.

Ok, that’s enough theory.

Other Uses for INDIRECT

1. Generate a Reference on the Fly

By nesting it with other functions we can generate references like this one:

=SUM(INDIRECT(“B27:B”&ROW(B35)))

In this example we’ve used the ROW function which returns the row number of the selected cell, in this example it’s 35. Our formula above evaluates to:

=SUM(B27:B35)

Well why didn’t I just type in =SUM(B27:B35) I hear you say. And fair enough too.

The point of this example was to give you a taste of how you can nest other functions in the INDIRECT function to derive cell references. If it ‘tastes’ good I recommend you try nesting other referencing functions like COLUMN, ADDRESS, and CELL.

Referencing Other Worksheets and Workbooks

You can reference other worksheets and workbooks with the INDIRECT function too.

References to other workbooks must be formatted like this:

=INDIRECT(“ ‘[your_workbook_name.xlsx]your_sheet_name’!$A$3”)

References to other worksheets must be formatted like this:

=INDIRECT(” ‘your_sheet_name’!H34″)

Note 1: spaces between double quotes and apostrophe (“ ‘[your) are so you can clearly see all of the components. You don’t actually put a space in your formula.

Note 2: you can reference other worksheets and workbooks but if you reference another workbook it must be open otherwise you will get a #REF! error.

2. Use INDIRECT with a Named Range

This is probably the second most useful application for the INDIRECT function.

Let’s say you’ve got 4 named ranges of data; North, South, East, West.

You’ve got a drop down list (data validation list or combo box) that allows you to choose from the 4 different regions (with names that are exactly the same as the named ranges you’ve set up for your regions).

When you make your choice the report automatically updates with the relevant figures without the need for any IF Functions or other jiggery pokery.

You can basically use the INDIRECT function in any formula that requires a reference to a cell or range of cells.

Download the workbook and look at the examples in more detail.

If you liked this then please click on the Facebook ‘Like’ button below to share with your friends, and then sign up for our Excel Tips & Tricks Newsletter.

For some of us we use the VLOOKUP function all the time and for the most part is does exactly what we want, but what if you want to lookup multiple columns?

Taking the example below; in cell B3 I have a data validation list that allows me to choose the player I want to look up.

Then in cell C3 I have the SUM of the Pay Rises for 2004 through to 2006 for that player. i.e. the values in columns D, E and F.

With a bit of help from an Array formula we can use our trusty VLOOKUP to do just this.

VLOOKUP Multiple Values Formula

In cell C3 I used the following formula to achieve this multiple VLOOKUP result:

{=SUM(VLOOKUP(B3,Table1[[Name]:[Pay Rise 2006]],{4,5,6},FALSE))}

Note: This is an array formula and so the curly brackets at the beginning and end are entered by Excel automatically when you enter the formula by pressing CTRL+SHIFT+ENTER but you need to type the curly brackets in the middle around the {4,5,6} as Excel doesn’t automatically enter these. More on Excel array formulas.

The Syntax for the above formula is:

=SUM(VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]))

Breaking each component of the formula down:

1. lookup_value: B3 – This is the name we choose from the data validation list.
2. table_array : Table1[[Name]:[Pay Rise 2006]] – Our table or data range is an Excel Table hence the data range has the name ‘Table1’. You could easily replace this reference with a regular data range e.g. $A$8:$F$34 or a named range.
3. col_index_num: {4,5,6} – Usually the column index number will be just one column in your table, but because we want to reference 3 columns, (Pay Rise 2004, 2005 and 2006), we’ve used an array which houses the 3 columns we want to reference (the array is defined by the curly brackets).
4. [range_lookup]: FALSE – this simply instructs Excel to find an exact match for the
5. SUM – Sum the results from columns 4, 5 and 6.

If you liked this please click the Facebook Like button below and then sign up for our Free Excel Newsletter for more tips like this.

I’m working on a dashboard for a client and I’ve set it up so that they can choose which month they want to display in their report.

When they choose a new month the header in the report automatically updates to show the new ‘From’ and ‘To’ dates.

New data is added daily, so at times the period of the report may only be a partial month.

So, how do I find the ‘To’ date for my dashboard header?

Essentially I want to find the largest date for the chosen month.

Let’s say the dates are in a column with a named range db_date, and we currently have data for 1^st January through to 24^th November 2011.

We know that the LARGE function can find the largest date in the whole range.

So =LARGE(db_date,1) would correctly find the date of 24^th November.

But what if they chose October? They’d still end up with a ‘To’ date of 24^th November when it should read 31^st October.

What we need is a LARGE IF function that said; find the largest date that is >= the ‘From’ date and <= to the last day of the ‘From’ month.

Unfortunately there’s not one function, but we can create a formula with an array like this:

{=LARGE(IF(db_date>=D3,IF(db_date<=EOMONTH(D3,0),db_date,0),0),1)}

In D3 we have the first day of the chosen month.

Remember with array formulas you enter them by pressing CTRL+SHIFT+ENTER and shazam, Excel magically puts the curly brackets in for you.

In English our formula reads:

=LARGE(IF(the date in the db_date column is >=D3, and IF(the date in the db_date column is <= the last day of the month in cell D3), find the LARGEST date in the db_date column that meet this criteria)

The EOMONTH function returns the serial number of the last day of the month before or after a specified number of months.

So =EOMONTH(D3,0) gives you 30/11/2011 (where D3 contains the date 1/11/2011)

=EOMONTH(D3,1) gives you 31/12/2011

And =EOMONTH(D3,-1) gives you 31/10/2011.

Notes on EOMONTH:

1. It is not recommended that you enter your dates as text e.g. =EOMONTH(“1/11/2011”,1). Instead refer to a cell containing the date or use the DATE function:

=EOMONTH(DATE(2011,11,1),1)

2. Excel 2003 users will need to install the Analysis ToolPak add-in to use the EOMONTH function.

• On the Tools menu, click Add-Ins.
• In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.

Bonus Use for EOMONTH Function:

=EOMONTH(NOW(),0)

Gives you the last day of the current month.

Use it at your will.

For more on the LARGE Function.

For more on Array Formulas.

Do you have a different way of tackling this? Let me know in the comments below. I’d love to hear your ideas.

And don’t forget to sign up for the weekly Excel newsletter below and get great tips to your inbox each week.