Microsoft Office Tutorials and References
In Depth Information
As mentioned earlier, you can use the nested IF function structure to accom-
modate up to 8 comparisons. If your comparisons exceed 8, I recommend vertical
( VLOOKUP ) and horizontal ( HLOOKUP ) lookups. We will learn more about
these functions in later chapters. These functions make comparisons of a cell value
to data in either vertically or horizontally oriented tables. For example, assume that
you are calculating marginal tax rates. A LOOKUP will compare a particular gross
income, to values located in a table, and return the rate associated with the gross
income. As you can see, the concept is to compare a value to information in a table
that converts the value into another related value, and then returns it to the cell.
Lookups are performed often in business. These functions are very convenient, even
when the possible comparisons are fewer than 8, since it may be necessary occa-
sionally to change a table value used for comparison; thus, it can be done directly in
the LOOKUP table.
4.3.4 Data Conversion of Text from Non-Excel Sources
Finally, it is not unusual to receive data in a text file. This data may be in tables that
we request from other sources, or data in a non-table format in a text file. Consider
the following example. Suppose we request data from a sales force (three sales
agents) regarding sales of a particular product for their three largest accounts. In
particular, you are interested in the agents providing the names, age, and quantity
of year-to-date sales for their top three accounts. Depending on their understand-
ing of Excel, you are likely to receive data in numerous forms, and quite likely
in a text file, either as a table or simply as word-processed text. Our job is to
insert this data into a worksheet, where we can examine it closely and perform
analysis.
Your initial reaction upon receipt of the data might be frustration—Why can’t
these sales people simply learn to use a spreadsheet? In fact, you wish they would
use a spreadsheet that allows entry of their data in a standard format, using the
Fo rms and Validation capability of Excel we have already studied. Let’s assume we
solicit data from the 3 sales agents. One sends an MSWord file containing a table,
another sends an MSWord file with data delimited with tabs (data elements that are
separated by tabs), while the last (and the laziest) sends a Word file with no data
delimiters other than spaces. Exhibit 4.8 shows the three documents received. Note
that the data contains three records with three fields for each sales associate.
Transcribing the table, section a of Exhibit 4.8, is quite easy. Simply copy the
entire table in the Word document by capturing all cells and applying the copy com-
mand, and then find the location in your Excel sheet where you want to locate the
data. The paste command will then place each item contained in each cell of the
table into a corresponding cell of the worksheet. Thus, the mapping of the Excel
worksheet data will be exactly that of the original text data. The paste also trans-
fers the format of the table. For example, the shaded titles will be transferred to the
worksheet causing those cells to also be shaded. This type of transfer is a preferred
Search JabSto ::




Custom Search