|
|
By Debra Dalgleish, on May 9th, 2013 There is a new sample file on my website, in response to a lookup question that someone asked on my Contextures Facebook page.
I'm staring at a huge spreadsheet showing the distances in miles between a few hundred job sites…Our data is accurate, but the users often enter the wrong mileage data because it's easy to make a mistake when scrolling…
How can I automate this process so that I can just enter the departure site and the arrival site and retrieve the distance between the two?
The Mileage Table
To find data in a lookup table, based on the
Continue reading Get Mileage from Excel Lookup Table
By Debra Dalgleish, on May 7th, 2013 It’s amazing how all those little expenses can add up over a year. For example:
Upgrade your cable package for an extra $30 per month, and that’s $360 more per year. Buy your lunch for $15 each workday, instead of bringing a $5 lunch from home, and you’ve added $2500 to your annual expenses. Compare Expenses in Excel
Instead of ignoring those extra expenses, you can use Excel to calculate annual totals, and see what happens if you can cut costs.
I’ve created a workbook where you can compare two scenarios, and see the difference in annual costs.
First,
Continue reading Calculate Annual Costs and Savings in Excel
By Debra Dalgleish, on April 23rd, 2013 It’s been a bad week for Excel, with big news stories about spreadsheet errors. Two Harvard professors were in the news after a student at the University of Massachusetts found errors in their economic research paper.
For example, in one of the formulas, five rows of data were omitted, so the average was incorrect. It’s easy for that kind of mistake to happen, especially if you add new data, or rearrange things, and forget to adjust your formulas.
You’ve probably read all about it, but if not, here are links to my favourite articles on this debacle:
BBC: The Mysterious
Continue reading Catching Your Excel Errors
By Debra Dalgleish, on April 16th, 2013 If you’re using a pivot table, there are built in features that lets you show a running total, or a percent running total.
If you’re using worksheet formulas, instead of a pivot table, there’s nothing built in that will automatically create a running total for you. Fortunately, with a simple SUM formula, you can calculate the running total in each row, to see how your bank account is doing.
Use the SUM Function
In this example, there are withdrawal and deposit amounts in cells C2:C6. We could use the SUM function to total the amounts in column C, and
Continue reading Create a Running Total in an Excel Column
By Debra Dalgleish, on April 4th, 2013 Last week, I heard from Kevin Lehrbass, who runs the My Spreadsheet Lab website. Kevin has posted an Excel video on YouTube, that shows how you can make a dynamic hyperlink, using array formulas.
Select a country from a drop down list, and click on the hyperlink to go to the selected country’s largest city. Well, you’ll go to that location in the table – you won’t actually end up in the city!
Select a Country
At the top of the worksheet, Kevin created a drop down list of countries, by using data validation. You’ll start by selecting a city
Continue reading Create a Dynamic Hyperlink
By Debra Dalgleish, on March 19th, 2013 In Excel, you can use the SUMIF and COUNTIF functions, to sum and count values, based on criteria. Did you know that you can also calculate an average, based on criteria?
Average with One Criterion
If you only have one criterion, you can use the AVERAGEIF function. In the screen shot below, the average quantity is calculated for any orders where Pens were sold.
=AVERAGEIF(A1:A10,"Pen",B1:B10)
Average for Multiple Criteria
If you need multiple criteria for the average, and you’re using Excel 2007 or a later version, you can use the AVERAGEIFS function.
In the next screen shot, the AVERAGEIFS
Continue reading Excel Average Based on Multiple Criteria
By Debra Dalgleish, on March 14th, 2013 This week, Dick Kusleika posted his Amazon Linkerator – an Excel file lets you create links to Amazon products.
First, you find a product on Amazon, and copy its web page URL. Then, open the form, enter a product code and description, and it creates a link for you.
It’s very fancy, and you can download the sample file, to try it for yourself. It uses an Excel UserForm, and you can modify the code to add your own information.
Jimmy Pena has an Amazon Link Builder too, and you can see the details here: Amazon Link Builder
Continue reading Create Amazon Affiliate Links in Excel
By Debra Dalgleish, on February 26th, 2013 First, some news about the Office 365 launch, and then a tip for summing values for a specific date range.
Office 365 Launch
Instead of desktop versions of Office, Microsoft is encouraging people to subscribe to an online version. If you'd like to see a preview of Office 365, you can attend Microsoft's online launch of the Office 365 product tomorrow, Wed. Feb. 27th. There are two sessions:
8 AM Pacific Time 5 PM Pacific Time
To register, click here, and fill in the registration form: Office 365 Launch Registration
There are lots of required fields in that registration
Continue reading Sum For a Date Range in Excel
By Debra Dalgleish, on February 14th, 2013 If you’ve been following the Canadian news (and who isn’t?), you know that the penny has been eliminated from circulation. To honour the occasion, Google made a special doodle for google.ca on February 4, 2013.
Rounding Guidelines
If you’re shopping with cash now, the final amount will be rounded up or down, to the nearest nickel. There are guidelines posted on the Royal Canadian Mint’s website: Eliminating the Penny: Rounding
Rounding to the Nearest Cent
As an example, the Mint’s website shows the purchase of coffee and a sandwich, with tax, for a grand total of $4.86.
The
Continue reading Round to a Nickel in Excel
By Debra Dalgleish, on February 5th, 2013 Last week, we took a look at the new FORMULATEXT function in Excel 2013. Another one of the new features in Excel 2013 is the ISFORMULA function. Finally, there is a way to identify cells that contain a formula, without creating a User Defined Function to do the job.
The TYPE function was originally designed to show what a cell contained, such as text or a formula. It returns a number to show the type for a cell's contents, or a formula's result. Here's the list of results, and the data types:
In a few versions of Excel,
Continue reading New ISFORMULA Function in Excel 2013
|
|
Recent Comments