User Manual

11
Chapter 1 — The Four-One-One of Excel
One of the most common requests from corporations that have utilized Excel was
to find the average value of cells where the cell met a certain criteria. So for those
of you looking at your monthly budget and you want to find out where you spend
more than $500 every month (probably at the Louis Vuitton store!), this is a great
place to start. After you enter all your expenses you can use the cool new formulas
the Excel 2007 team has created to summarize your data given a certain condition.
In the following list you can find a couple of the new added functions with Excel
2007 that enable you to get the most out of your data. If you want to find out more
about functions, please read Chapter 6.
AVERAGEIF: Helps you to find the average value of cells in a range for cells
that meet a single criterion. So in this case if you want to find out where
you spend more than $500 in monthly expenses, this function is how you
would do it. You would start by typing “=AVERAGEIF(“ (including the
quotes, of course) into a cell and continue by selecting the cells you are
looking at.
AVERAGEIFS: Helps you find the average value of cells in a range of cells that
meet multiple criteria. So if you wanted to find out in which month you spent
$500 on your shopping bill that would be two criteria: shopping and $500
minimum. So you would select the shopping column and then the cell for each
month where you spent your shopping money.
File Formats and How to Share with Your Friends
With the new Microsoft Open Office file format XLSX (.xlsx) you can integrate better
with other programs such as Open Office or other types of comparable office prod-
ucts. You can also integrate better with other types of data, such as if you want to copy
pictures or your Christmas wish list to a blog, Amazon.com, eBay, or other places. As
you grow your Excel 2007 skills and want to connect to data sources such as Access,
SQL database, or others, it is easier than ever with Excel 2007. You can even begin
by clicking on the Data tab of the Office 2007 Ribbon. So what this means, ladies, is
more room for pictures of all the wish list items you have and room for music and video
lists. So the next time you make a wish list on Overstock.com, you can put that list on
your desktop and save it for later use. But this also means that if you want your friends
and coworkers (who may not have been as lucky as you to have gotten a copy of Excel
2007) to be able to read the spreadsheets you create, you will have to save in the
97-2003 format. Some things might change (such as shading or font styles that were
unavailable in older versions of Excel) but most of what you see, they will see.
In order to save properly, click on the Office Pearl from Figure 1-1 and choose the
Save As option (Figure 1-9) from the drop-down menu. Once you click on the Save
As option, you will see a window like the one in Figure 1-10 come up. This window
is where you can make sure you are saving to the right location, with the right name,
and to the right format (pay close attention here or your friends can’t see it!), as
shown by the bottom arrow of Figure 1-10.
05 149164-ch01.qxp 7/16/08 2:17 PM Page 11