18- Working with advanced functions
181- Concatenation
·
Let’s say
we want to have three of our column data in one column
·
at the
end of our table. This would give us a quick reference.
·
Or we
might want to delete 3 columns and replace one column
·
with the data
from all three.
·
First we
place the cursor where we want the new text to be
·
placed.
·
Press the
Formula Tab, click on Text then on Concatenate. Then
·
enter the
cell references separated by a _ to distinguish from one
·
cell to
another. The first cell we would enter is the ITEM cell which
·
is A2
this is too be placed in the first text box of the box that
·
opens up.
In the second text area, we would place _ in the third
·
B2, in
the fourth _ and in the fifth C2. Excel 2007 then shows us in
·
text what
the completed function will look like. That is the result
·
what we want so we click on OK.
Once you have completed the function, you will see the
results on your worksheet, click and drag to copy the function down the column.
We can now select the row, click on copy, go to the
place where we want the new information A2 and click Paste Special, then Values.
182- Sum if
Insert the following table and enter the data. What we
are going
to do with this table is find the total amounts of
what’s been sold
But if we had a very large workbook, we would find it
takes ages
to count the number of Laptops, say. So we are going
to do a
function to give us the information.
Type in the data n column E
Place your cursor where you want the new data to go.
Place it in cell F2 until it is surrounded by a black
box. As shown
previously.
Go to the Functions area, in the Formulas Tab, click
on the maths
& trig option. From the fly‐out choose SUM
IF
From the dialogue box that opens up we enter the data.
In the
Range Box, we must enter the range of cells where we
want to get
our data from. A2:A13 In the Criteria we tell Excel
what to find
when doing the sum. In the Sum Range we give Excel the
cells
where it can sum up. B2:B13.
When we copy the cell down, it gives the wrong answer,
we must
remember to format the new cell with the correct
information.
There we have it, the total amounts sold of each item.
183- Lookup
Look Ups:
These types of functions are very powerful functions
in Excel 2007.
VLOOKUP:
Choose VLOOKUP from the fly out in the Lookup and
Reference
category in the formulas tab. VLOOKUP is the easiest
type of
Lookup function. It looks up information in columns. A
HLOOKUP
function would find the information on a row, so in a
work
situation it would depend on how the tables are set
out as to what
kind of Look up you would use.
In this exercise we are going to find data based on
the item
number to find out the cost of the item. The VLOOKUP
function
searches in the first column for the data. If we had a
large amount
of data this function would cut down the work.
Here we have the function box, if you read the text on
this box,
you will get an idea of what the VLOOKUP function
does. We need
to enter in 4 references for the function. The first
text box is the
lookup_value and will be looking at the data in the
first column of
our table for our result.
Enter in F7 as that is the cell we want the value in.
The Table_array is the whole table, Enter in A2:E13 we
do not
need to include the headings.
The next box is Col_index_num in this box we would
enter in the
cost of each item which is in Column 4, notice we
enter in the
number of the column and not the cell reference, we
find the number by simply counting the columns until we come to the right one.
The final box asks for the Range_lookup, because we
want an exact match we will enter FALSE.
Our arguments box now looks like this. Click on OK.
The Table above now looks as if we have entered in the
wrong formula but we have not, it is simply looking for something to look up.
In F7 enter 4 under Item No, in cell F7 press enter
and you should have a result, check manually to see if it is correct. If it is
not check the formula on the next page, there is probably something that
you have not entered in correctly. Lookup is a very
complex formula, so well done for getting this far. Try more amounts of the Item
No.
184- what-if analysis
What
is Goal Seek?
Goal Seek is a built in Excel tool that
allows you to see how one data item in a formula impacts another. You might
look at these as “cause and effect” scenarios. For example, you might be
looking at your local election results and see:
* Needs approval from 2/3 of the voters
In our example, the YES votes are a majority,
but shy of the required 2/3 approval to win the election. People quickly
realize they were close, but which item do they change to find out how close.
What would've made a difference?
Using Goal Seek we can change the value of
various cells to see how the results change. This would allow you to answer
these types of questions.
In each of these questions, the goal is to
change a data value to see if the YES percentage went over that two-thirds mark
or 66.67%. Rather than haphazardly changing cell values to see the results,
Goal Seek can find the answers.
How
to Use Excel Goal Seek:
1)
Create a spreadsheet in Excel
that contains your data.
·
Click the cell you want to change. This is called the “Set cell”.
·
Click the cell you want to change. This is called the “Set cell”.
2)
From the Tools menu, select Goal Seek…
3)
In the Goal Seek dialog, enter
the new “what if” amount in the To value text box. (Remember
to add the percentage sign if you have one.)
In this example, we're asking Excel to replace the contents of cell D4 which is 63.90% with 66.67%. This is the percentage needed to win the election. 4) We also need to tell Excel which cell to change. Since we wanted to know the number of YES votes, we'll click C4.
5)
Click OK. Excel
will overwrite the previous cell value with the new one.
6)
If you wish to accept the new
value, click OK
Goal
Seek & Requirements:
If you
look closely in the Goal Seek Status box, you'll see the Target value: reads: 0.6667 came from our 66.67%. But the Current value: shows as 66.64%. Excel rounded down and the revised YES value wouldn't win the
election since 66.64% is less than 66.67%.
Another group may be asking why Excel didn't
just take 66.67% of the TOTAL vote count 7008. And that leads us to a
requirement of Goal Seek – formulas. The Set cell in Step 2 must
contain a formula. In our example, the initial 63.90% was a formula
using the YES value (C4) divided by TOTAL value (C6). So, we met that
requirement.
If I just typed the value 63.90% and tried to
use Goal Seek, I would see an Excel error stating “Cell must
contain a formula”.
Just as a used a formula in my set cell, I
also used a formula for TOTAL in B6. This was the Sum of my YES and NO votes.
Since this TOTAL cell was a formula, my TOTAL count automatically adjusted when
Goal Seek changed the YES cell value. If I typed “7008” in cell C6, only the
YES cell value would have changed.
The other requirement is the cell you
change in Step 5 can't contain a formula. It must be a typed value.
As this example shows, Goal Seek is a nice
tool that can quickly find the answers to different situations. Although this
was a simple example, you can use the same tool for complicated spreadsheets
containing many variables and formulas. Just remember, you many not always like
the results.
Back To Top Back To Excel Index Page (Open Exercise file)
|
||||||||||||
