Tag Archives: london training

Excel Training: Use Goal Seek for What If Scenarios

Excel has some great functions for “What if…” scenarios. One tool that can be used is the Excel Goal Seek. This allows you to choose any current formula and set the answer value to your own goal.

e.g. If you have a formula that works out how much profit you make from selling items, such as £20, but you would like it to be £30 profit, Goal Seek can change various cells to achieve that £30 goal.

Below is an example similar to ones we use on our Excel Training courses based in London. We have a simple loan calculator that uses the PMT function to work out monthly loan repayments.

Excel Goal Seek Tool

 

 

 

 

 

Open the Goal Seek from the Data Ribbon, Data Tools Section, What If Analysis.

Excel Data Tools Goal Seek Ribbon Bar

The Goal Seek dialog appears. Fill out as follows: Enter the cell you wish to set your goal to in Set Cell Field. Enter the figure you want the Goal to achieve in the To Value Field. Enter the variable (cell) you would like Excel to change to reach your set Goal in the By Changing Cell Field.

Excel Goal Seek ToolClick OK

The Sheet content will change to reflect the Goals set. You will be presented with another dialog box, Click OK to keep the changes, or Click Cancel to revert the cells back to original values.

 

Using COUNTIFS with Excel 2003

Excel 2007 introduced the function COUNTIFS to count cells based on multiple criteria.

For example suppose you wish to count how many times the client Fowler buys more than 250 shares. The answer for tha data below turns out to be 2 using the Countifs function entered in B13.

Similarly for Owen the result turns out to be 1. 

 

 

 

 

 

 

 

 

 

Even though COUNTIFS is not availailable with Excel 2003 there is an alternative way to perform the same calculation using the SUMPRODUCT array formula.

The formula

 =SUMPRODUCT(–(A2:A11=”Fowler”),–(C2:C11>=250))

typed into C13 produces the same result. The mysterious looking — operator calculates if true or false for each of the cells in the range A2:A11 returning 1 if true and 0 if false. The same goes for the range C2:C11 and the Sumproduct array function sums all the true, true (1*1) combinations.

Note – To show how – works try typing –(A2:A11=”Fowler”) into a cell followed by Ctrl+Shift+Enter. This convets the formula into the array formula {=(A2:A11=”Fowler”)}

Excel 2010 Courses – Calculating Times

One of the most asked questions during our Excel 2010 Training Courses in London has to be the subject of calculating times.

One important issue is how time values are typed in Excel. This should always be as
9:00 AM or 10:30 PM. There must be a colon separating the hours:minutes and if you are specifying AM or PM there MUST be a space between the minutes and the AM or PM.

In the example below we wish to calculate elapsed time.

The formula would be the later time minus the earlier time. E.g. 10:25 AM – 9:00 AM

The result will be formatted incorrectly. To format to show 1.25 you Select the Cell with the answer. Right-Click, From the context menu select Format Cells

Select Custom from the Category list, then choose the Type [h]:mm:ss

Note: You MUST select the format with the square brackets around the h.
Optional, you can remove the :ss from the end, unless you need to see the Seconds.

Click OK. This will display the correct format.

The other problem people on Excel Training Courses experience is when the time spans over midnight. This can give negative results. The secret is a function called MOD.

The last example from the above screen shot shows 10 PM from the previous evening and an end time the following day of 6 AM. The calculation would still be the later date/time minus the earlier one e.g. 6 AM – 10 PM

However, include this within the MOD function as below:

Microsoft Training Excel 2010 - MOD Function

Type: =MOD(Latest time – Earlier time, 1 )

This will give the correct Hours/Minutes elapsed between the times.

Excel Training – Retrieve Unsaved Excel Workbooks

Ever closed an Excel workbook and clicked the “Don’t Save” option, then realised you should have saved it! Microsoft Excel 2010 now has a file recovery option for just such an occassion. This has proved invaluable in our Microsoft Excel Training Courses in London.

Always check the AutoRecover time in the Excel options. To do this go to the File, Options and Save. (Shown below)

Set the “Save AutoRecover information every” time to 2mins.

London Excel Training Courses - Excel 2010 Options Dialog

Retrieving a workbook after it’s been closed without saving can be achieved by:

File Ribbon, Info

Microsoft Courses in London - File Menu

From the Info panel select the Manage Versions button, then Recover UnSaved

London Training Microsoft - Manage Versions Image

A list of unsaved documents will be seen. Select the one required.

Note: This works for both documents that you have previously saved, and docs never saved