adv excel practice tools2011

7/28/2019 Adv Excel Practice Tools2011

1/19

This template was purchased by AuditNet from a third party under a work forhire agreement. However, while we have attempted to provide accurate

information no representation is made or warranty given as to the

completeness or accuracy of the template. In particular, you should be aware

that the template may be incomplete, may contain errors, or may have become

out of date. While every reasonable precaution has been taken in the

preparation of this template, neither the author nor AuditNet assumes

responsibility for errors or omissions, or for damages resulting from the use of

the information contained herein. The information contained in this document is

believed to be accurate. However, no guarantee is provided. Use this

information at your own risk.


2/19

21 Advanced Excel Tools:

Scenarios

Histogram

8 types of Charts

Projects

Loan Worksheets

3 types of Picture ChartsDepreciation

5 types of Lookups


3/19

Scenario Summary

The Scenario Summary tool is a what-if analysis tool that can

be used in forecasting outcomes. Several what-if scenarios

can be saved and viewed to show projected outcomes.


4/19

Histogram

Description

A frequency distribution shows how often each different value in a set of

occurs. A histogram is the most commonly used graph to show frequenc

distributions. It looks very much like a bar chart, but there are important

differences between them.

When to Use a Histogram

When the data are numerical.

When you want to see the shape of the datas distribution, especially wh

determining whether the output of a process is distributed approximatel

normally.

When analyzing what the output from a suppliers process looks like.

When seeing whether a process change has occurred from one time per

another.When determining whether the outputs of two or more processes are dif

When you wish to communicate the distribution of data quickly and easi

others.


5/19

data

y

en

iod to

erent.

ly to


6/19

3-D Charts Pyramid Charts


7/19

6 types - Charts


8/19

ABOUT GANTT CHARTS

A Gantt chart is a graphical representation of the duration of tasks against the

planning and scheduling projects.

A Gantt chart is helpful when monitoring a project's progress. A Gantt chart is

Gantt charts illustrate the start and finish dates of the terminal elements and su

Terminal elements and summary elements comprise the work breakdown struc

dependency relationships between activities.

Gantt charts can be used to show current schedule status using percent-compl

Planning and Scheduling

Use a Gantt chart to plan how long a project should take. A Gantt chart lays ou

Early Gantt charts did not show dependencies between tasks but modern Gantt

Henry Laurence Gantt, an American mechanical engineer, is credited with the i

Monitoring a Project

A Gantt chart lets you see immediately what should have been achieved at any

A Gantt chart lets you see how remedial action may bring the project back on c


9/19

rogression of time. A Gantt chart is a useful tool for

type of bar chart that illustrates a project schedule.

mmary elements of a project.

ure of the project. Some Gantt charts also show the

ete shadings and a vertical "Today" line.

the order in which the tasks need to be carried out.

chart software provides this capability.

vention of the Gantt chart.

oint in time.

urse. Most Gantt charts include "milestones."


10/19


11/19

Charts - Averages, Performance and by Region


12/19


13/19

Depreciation: SLN, DB, DDB, SYD


14/19

LOOKUP Vector Form

The vector form of lookup takes 3 arguments; 1) lookup_value 2) lo

result_vector). The lookup_value is the number of the primary key.

where the desired item is found.

Practical Application of Vector

Use of vector form

Finding which last names are paired with a specific ID is a practical

this information. However, when managing thousands of rows of da

gather information. For example LOOKUP(33807,A2:A2000,B2:B200

LOOKUP Array Form

The LOOKUP array form takes two values, a lookup_value and an arFor example LOOKUP(A5,{0,6, 8,10},{"terrible","bad","average","gre

It then index matches the value to the item in array 2, i.e. 0=terrible,

Such a function is useful because it helps the developer query a sp

Commonly Used Options

Vlookup, Hlookup, Lookup, Match Index & Compare


15/19

For example, VLOOKUP (Vertical LOOKUP) is useful for searching a

HLOOKUP can also search across a wide range, but interacts mainl

MATCH is another option in the LOOKUP family that returns the nu


16/19

kup_vector

he lookup_

application

a this beco

) would fin

ay/s.at"}) looks

=bad etc.

cific item


17/19

cross sever

with top r

erical posi


18/19

and 3) result_vector. The statement is written as LOOKUP(lookup_value, lookup_vector,

vector is the range of cells for the lookup_value, and the result_vector is the range of cells

for utilizing LOOKUP in vector form. Alternatively, one could use the search option to obtain

mes very time-consuming. LOOKUP in vector form offers a convenient and expedient option to

which last name is classified under the ID "33807."

p the value in A5 and takes the same or nearest least value.

ithout having to extensively navigate data.


19/19

.

al columns and rows on each query.

ws and within row data.

ion of a queried item within a column.

adv excel practice tools2011

Documents