Post image for Excel Evaluate Formula Tool

Excel Evaluate Formula Tool

by on February 22, 2012

in Excel,Microsoft Office Training,Online Training

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 [...]

Post image for VLOOKUP Multiple Criteria

VLOOKUP Multiple Criteria

by on February 15, 2012

in Excel,Microsoft Office Training,Online Training

I had an email from Bobcat today asking how to lookup data that is spread across multiple columns. Table 1 has data with the name in just one column:     And Table 2 has the name across 3 columns (D, E and F):     We want to find the Employee number from Table [...]

Excel Data Validation With Dependent Lists

February 8, 2012
Thumbnail image for Excel Data Validation With Dependent Lists

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 [...]

read more

What If Analysis Using Excel Scenarios

February 3, 2012
Thumbnail image for What If Analysis Using Excel Scenarios

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 [...]

read more

Excel AVERAGE, AVERAGEIF and AVERAGEIFS

January 27, 2012
Thumbnail image for Excel AVERAGE, AVERAGEIF and AVERAGEIFS

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 [...]

read more

ROUNDUP and ROUNDDOWN with a Twist

January 18, 2012
Thumbnail image for ROUNDUP and ROUNDDOWN with a Twist

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 [...]

read more

INDEX MATCH With a Twist

January 12, 2012
Thumbnail image for INDEX MATCH With a Twist

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 [...]

read more

Excel INDIRECT Function

December 14, 2011
Thumbnail image for Excel INDIRECT Function

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 [...]

read more

Excel VLOOKUP Multiple Values

December 7, 2011
Thumbnail image for Excel VLOOKUP Multiple Values

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 [...]

read more

Excel LARGE and EOMONTH Array Function

November 30, 2011
Thumbnail image for Excel LARGE and EOMONTH Array Function

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 [...]

read more

← Previous Entries