Tag Archives: Excel Web App

how to use goal seek in excel

How to Use Goal Seek in Excel

Excel has proven itself to be very useful in various situations over and over again. The list of Excel’s benefits seems to be never-ending.

It even has a tool for answering questions and forecasting information. The Goal Seek function in Excel is a great tool for those asking “What if” type questions.

Use this guide to learn how to use goal seek in excel as well as how to put it into action.

Why Use Goal Seek?

why_use_goal_seek_

 

The Goal Seek feature in Excel is basically used to create formulas. It provides information on cause and effect situations.

It determines what specific data will impact another set of data. This feature comes in handy in lots of scenarios.

In financial and sales situations, it can be used to determine what must be accomplished in order to reach a certain monetary goal.

It can also be used to calculate how many votes a candidate needs to win an election.

Once you have your Goal Seek formula in place, you can change any part of it to see how the formula as a whole change.

For example, a computer sales company has a profit goal of $3 million for the year.

Each of their computer systems cost $200, while additional accessories like keyboards cost $50 extra.

This company could use how to use goal seek in excel to determine how many of each product they’ll need to sell to reach their goal.

Here’s How To Use Goal Seek In Excel

To use Goal Seek, you will have to open a new sheet and enter the current information you have.

To make learning Goal Seek a little easier, we are going to use a simpler example.

Sample Scenario

how to use goal seek in excel

How To Start

To start, enter your information and functions into a spreadsheet in an organized table.

For our data, we’ll have the headings Playground, Movie, and Total in the first column.

Across the top, we’ll have the headings Votes and %. We have set up our table to calculate percentages base on the votes.

The goal will be to determine how many votes will it take to choose the Playground option.

Next Move

The next thing we need to do is select the cell we want to change. We want to make the Playground vote equal 66%, or 2/3, so we will select that cell.

Under the Data tab, locate the “What If Analysis” button. Click it to reveal the drop-down menu.

On this menu, you will see “Goal Seek.” Select this option, and a dialogue box will appear.

The first option you will see will ask you to set your cell. In our case, the percentage of Playground votes cell is D2, so we will enter that into the box.

Next, you will see “To value.” This is where you enter the desired goal. Our goal is 66%, so we will enter that into this box.

Final Steps

Finally, the last box will say “By changing cell.” This will be whichever cell you want to change in order to get your result.

We want to know how many votes will get us to 66%, so we want to change the number of playground votes. In our case, that is cell C2.

After you input all of your information, hit OK. Your information on your table will change to accommodate the goal number you have set.

In our case, we change the playground percentage to 66% based on the number of votes it needs.

When we hit OK, the numbers on our chart revealed that 24 out of the class’s 37 students would have to vote for the playground.

That is for them to reach 66% and establish a fair vote.

The Goal Seek function also shows us that for this to be true, 13 students will have voted for the movie.

Conclusion

 

how to use goal seek in excel

The Goal Seek function can be a very useful tool when trying to forecast sales, finances, votes, etc.

Numbers can be tricky to determine, and in important situations, it can be crucial to making decisions based off of accurate information.

The Goal Seek serves to perform exactly that.

Upon first use, the function can prove to be a little tricky. An important thing to remember is to make sure all of the functions in your table make sense.

The Goal Seek feature will not work properly if your functions don’t add up.

You may also get some funky numbers that don’t make sense if your functions don’t make sense.

It is a good idea to practice using this method a few times before you start using it for important projects.

Once you have the feature mastered, Goal Seek is an excellent tool for both professional and educational use.

Read through this guide on how to use goal seek in excel and follow the steps a few times to really nail down the skill.

This may come as a surprise to how useful it becomes. As a matter of fact, your business or job will be a lot easier.

Soon you’ll be forecasting information for all sorts of projects!

Convert Coordinates for Your GPS with Excel

A few years ago I found myself just south of Paris, France one Sunday with a car and the inclination to do some sight-seeing. Since I was alone and don’t speak French my saving grace was Google Earth and a Tom Tom GPS navigation device.

Tom Tom allows GPS coordinates to be entered as a destination, but Google Earth lists those coordinates in a different format. In researching this article I found out that GPS coordinates can be presented in at least four different formats, making it difficult to understand the coordinates.

Google Maps view of the Eiffel Tower

Click the x to remove the Address box and show the interactive Google Map.


View Larger Map

Use Excel to Convert GPS Coordinates for Tom Tom

One of my destinations was to see the Eiffel Tower and Google Earth shows the GPS coordinates to be 48 degrees 51 minutes 32.64 seconds North and 2 degrees 17 minutes 34.90 seconds East. (Coordinate formatting shown as 48° 51′ 29.69″ N 2° 17′ 38.02″ E in Google Earth while holding the mouse over a position on the map.)

However the Tom Tom GPS device wanted coordinate input in degrees only, where minutes and seconds represent the fractional part. So I created a quick spreadsheet to do the conversion. (Click the picture to download the .xls file.)

GPS coordinate conversion

To convert from Degrees, Minutes, Seconds to a Tom Tom degree format, the math is:

Degrees+(Minutes/60+Seconds/3600)

This allowed me to enter GPS coordinates directly into the Tom Tom GPS device. Since I wasn’t familiar with the street names or the numbering system for locating addresses on the Tom Tom, this was much faster than trying to find the correct address in the Tom Tom street directory. The Tom Tom GPS was a loner from an associate so that was another factor in my decision to use GPS coordinates.

Note: Tom Tom allows different formats for GPS coordinates, this example just mirrored settings for the GPS I was using at the time.

Use Excel to Convert GPS Coordinates for Garmin

When I got back home to the USA and tried this with my Garmin it didn’t work because the required format for GPS coordinates was different. My Garmin GPS device wanted Degrees and Minutes only, with seconds being the fractional part of minutes. That formula is different because it requires some concatenation with an empty space between degrees and minutes.

Degrees & ” ” & Minutes+Seconds/60

So my Excel spreadsheet helped me convert numerous GPS coordinates for my trip to Paris.

I had a memorable time.

Eiffel Tower Paris

Note: If you’re wondering about the North and East designations, click this link to see how to read GPS coordinates.

Convert GPS Coordinates with Excel

Here’s an interactive worksheet that you can use to figure out some GPS coordinates for yourself.

To download the worksheet using this link.

Cross-posted at vlatte.net.

Excel 2011 for Mac – Installation, Preferences, and VBA

After reading all the great things about Microsoft Excel for Mac 2011 that were written before its release, I decided to purchase a copy. My primary goal was to have an Excel version on my MacBook Pro that allowed VBA macros, however the full Office software suite seemed to be a really great value.

I paid $174.99 USD for the Microsoft Office for Mac Home and Business 2011 – 1 Pack edition on amazon.com after pre-ordering and receiving a $25 discount from their price assurance guarantee.

Excel 2011 Software Installation

What you get with the Office 2011 Home and Business Edition:

  • Word 2011
  • PowerPoint 2011
  • Excel 2011
  • Outlook 2011
  • Microsoft Office Web App support
  • Messenger for Mac 8
  • Remote Desktop for Mac 2 (drive your Windows-based PC from your Mac)
  • Technical support 1 year

The installation took about 15 minutes. The program loaded, I entered the product key, activated and registered the software, then launched Excel, and got a welcome screen telling me all about the new features.

Whats New Excel 2011

And then I had to face the Excel Workbook Gallery, which is the default screen that appears when you open Excel 2011. Nice for all of 15 seconds.

At the bottom of this screen there’s a box you can check: Don’t show this when opening Excel, but I ignored it and went straightaway to a blank Excel workbook.

Excel 2011 Preferences

The first thing I wanted to check out was the Options, oops pardon my Windows speak, I mean Preferences, by using the menu selection Excel → Preferences or the keyboard shortcut Cmd+apostrophe ( ⌘ , ).

Excel 2011 Preferences

Within these preferences is standard stuff you’d find in a Windows version of Excel, but I want to review some of the settings for General, Edit, AutoComplete, Compatability, and Ribbon.

General Preference

In the General dialog box I unchecked the box beside: Open Excel Workbook Gallery when application opens so that Excel opens to a blank worksheet.

General Settings Change

Edit Preference

On the Edit dialog box, a new option for Excel 2011 is Automatically convert date system. As you can see in the Description box below, this option converts the date system of the source data to match the date system of the target workbook.

I’m not exactly sure how this preference option works and will be looking into it, but with Excel’s two different date systems (1900 & 1904) this setting becomes more important now that Excel 2011 and Excel 2010 can share workbooks on the web in Windows SkyDrive.

Excel 2011 Edit Dialog Box

AutoComplete Preference

In the AutoComplete dialog box the setting for Show the menu only after I’ve typed x letters (x set at 1, range 0-9) seems to be one that I’ll revisit soon. I’m not sure I want AutoComplete popping up after typing only 1 letter.

AutoComplete Dialog Box

CompatibilityPreference

On the Compatibility dialog box, under Transition, the Save files in this format: is defaulted to Excel Workbook (.xlsx) and can be set to a number of different things.

Excel 2011 Compatibility Dialog Box

Changing the default file format to Excel 97-2004 Workbook (.xls) might be prudent should you share files frequently with people using older versions of Excel.

Ribbon Preference

On the Ribbon dialog box, under Show or hide tabs, I noticed that you can drag them in the order you prefer. I added the Developer tab to the Excel Ribbon by checking the box for Developer.

Ribbon Dialog Box

The VBA Editor – A Quick Look

I had to peek at the VBA editor just to check it out. Going to the Developer tab and clicking Editor opened the VBA Editor, where I added a module and wrote a quick test macro, as you can see below.

Excel 2011 VBA Editor

Not a bad start.

Next I’ll compare the Ribbons of Excel 2011 and 2010.

Excel on the Web – Another Spreadsheet in the Cloud

I decided to test the new Microsoft Excel Web App and tell you about my experience in creating a simple spreadsheet, give you a look at the interface, and share my initial opinion.

The Excel Web App – Getting Started

In order to use the Microsoft Excel Web App a Windows Live account is required. It’s relatively easy to sign up for an account and you get 25 GB of storage on a Sky Drive. You also get access to the rest of the Office Web Apps: Word, PowerPoint and OneNote.

Excel Web App vs Google Docs Spreadsheet

The Excel web app is similar to a Google Docs spreadsheet. By that I mean the spreadsheet is just a simple, basic offering. It seems like the Excel version has an advantage because the tools are more familiar and it’s integrated with the Excel 2010 desktop software.

My First Excel Web Apps Spreadsheet

My goal was to create a spreadsheet with a series of dates in one column and a formula in a second column showing the associated day. Entering and formatting column headers was simple, and similar to the desktop experience. Entering a date into a cell was also easy because it was just a simple manual entry.

And here’s where the Excel web app diverges from what I would normally do on the desktop version. In order to generate a series of dates I had to use a formula adding one (1) to the initial date. In the desktop version I would generate a list of dates with the series dialog box as I shared in a previous post. Easy peasy.

Not to worry, I’ll just use the fill handle and drag down the formula. But wait! You can’t grab the fill handle because there isn’t one. So I copy the formula, selected the range I wanted, and pasted the formula. Whew! That was a little more work than I’m used to.

Next I entered the TEXT formula in the second column and it worked perfectly. Yet I still  had to copy and paste the formula into the range below because there’s no fill handle for me to double-click and shoot it down automatically. Oh well, just feels a little rustic.

Next I clicked the Open in Excel file icon and the program asked me for my Windows Live ID and password. (I checked an option so I wouldn’t have to do this every time). Excel 2010 then started up and opened the spreadsheet file on my desktop.

I made a couple changes in Excel 2010 and then saved the file. This action saved it directly to the website and not my computer.

Using the Excel Web App, I noticed the changes made by the desktop program. I also became aware of an option to look at the Version History, which now consisted of links to two different files.

Doing a simple sort in the Excel Web App was not so simple. That’s because the only option is Sort & Filter as Table, which turns the sort range into a table.

Finally, I downloaded the file to my computer and tried to open it, but neither Excel 2010 nor Windows could recognize the file type. That was because the Excel Web App saved the file with an underscore (_) before and after the file name. Since the file extension was .XLSX_ I had to remove the trailing underscore so the file could be recognized and open correctly.

The Excel Web App Interface

There are several things you can do with Excel files in the browser. In the picture below Version history will take you to a page showing links to older versions listed by date and time. Clicking the More drop-down will give you three options: rename, download, and properties.

Excel Web Document File

Selecting the Excel Web Test file icon will open the spreadsheet in View mode. Selecting Edit in browser will open the same file in Edit mode.

View mode is shown in the picture below. Notice the active cell is B2.

Excel Web Document View

Below is the file in Edit mode. The active cell is B2, but the formula is now visible in the formula bar. You can also see the Ribbon interface.

Excel Web Document Edit

When you elect to Share a file the following screen pops up to give you a link.

Excel Web File Share Link

The permissions link (above) brings you to another screen (below) to manage who can access the file.

Excel Web File Share Options

Excel Web App Menus

Shown below are the menus you get in Excel’s web app. They’re considerably abbreviated when compared to the desktop program, but hey, this is a web app.

The File Menu

Clicking the “Where’s the Save Button?” will inform you the spreadsheet is saved automatically.

Excel Web File Menu

The Home Menu

This menu is one continuous bar that I cut in two so it would fit here on this web page.

Excel Web Home Menu 1

Excel Web Home Menu 1

Excel Web Home Menu 2

Excel Web Home Menu 2

Notice Sort & Filter as Table menu item shown above. You can’t really sort with out having the program put the data into a table, but at least they let you know beforehand with a pop-up box.

Excel Web Sort as Table Pop-Up

The Insert Menu

Excel Web Insert Menu

Excel Web Insert Menu

My First-Look Opinion

I am a hard-core Excel user. And while I don’t mind going through a learning curve, the effort required should make me more productive, e.g. slicers, power pivot. The problem I have with web based spreadsheets is that you have to go through a learning curve, but the end result is being less effective because the tools and features are much less than the desktop version of Excel and productivity suffers.

However, there is a place for the Excel Web App. I’m just not sure where it fits for me. The lack of features is mitigated by being able to use the Excel 2010 desktop program. So for me it’s a better choice for an online spreadsheet then Google Docs. And I really like the idea of 25 GB of storage. I just have to figure out how to make use of it.

Related Posts Plugin for WordPress, Blogger...