Description
I have attached an excel spreadsheet.
Please follow instructions as listed on the sheet. If you have any
questions please send me a note ASAP.
MKT (##)
3
20171202122656activity_2.xlsx
Printed on 12/2/2017 at 1:27 PM
Course: Lean Logistics Operations
3
Section
1 -data
Exercise 3 Section 1 – Exercise 3
es, Autoshapes,
Linked
#
Student Name:
CMU Global ID
Date:
Professor
Class
Time
Function Name
1 BASIC LOGIC
=X=Y
2 BASIC LOGIC – STRING
=CONTATENATE(A,B)=CONTATENATE(C,D)
3 INTRODUCTION to IF Function
=IF(A=B,1,0)
Exercise #
1
2
3 (A)
4
5
6 (A)
6 (B)
12/2/2017
Simple matching techniques
Simple matching techniques for a string of information
Status
INCOMPLETE
INCOMPLETE
INCOMPLETE
INCOMPLETE
INCOMPLETE
INCOMPLETE
INCOMPLETE
Task Location
Student Activity Status
Exercise 1′ Tab
INCOMPLETE
Exercise 1′ Tab
INCOMPLETE
Exercise 1′ Tab
INCOMPLETE
An IF function consists of 3 different parts; the ‘Logical Test’ which tests whether a condition is met and is either ‘TRUE’ or ‘FALSE’, the ‘IF TRUE’ section, and the ‘IF FALSE’ section.
The IF TRUE and FALSE sections are where you enter your desired values for if your function meets the criteria and does not meet the criteria.
The IF function is a much more advanced logic function than a simple =X=Y. Using this formula you can include more advanced language in your logical test and even allow you to
have your results be based on formulas. You can also have the results of your IF functions to be other IF functions.
IF functions are under-utilized expressions that tend to turn off users because of their implied complexity. However IF functions are utilized properly they can drastically cut down
the amount of time it takes to evaluate data.
If you ever need assistance in understanding any excel formula, simply select the fx icon to the left of your formula bar
Locate the formula you are trying to search for
And excel will walk you through the various steps. In this case it will walk you through the basic components of an ‘IF’ function.
4 INTRODUCTION to IF Function
=IF(A=B,”OK”,”ERROR”)
Exercise 1′ Tab
INCOMPLETE
If functions are not confined to numerical data. You can have the results of a logical test validate text or values identified in a number of different ways. In this formula you are being
asked to adjust the results of your formula to be text. This is accomplished by using quotations marks before and after your text in your statements. If functions will become an
integral part of your exercises moving forward. They are covered at length in section 2 – exercise 1.
5 INTRODUCTION to IF Function
=IF((A&B=C&D,”MATCH”,SUM(C:D))
Exercise 1′ Tab
The ‘IF TRUE’ and ‘IF FALSE’ sections of IF functions open up endless application possibilities. Besides numeric values and simple text, you can also set these parameters as formulas.
In fact you can have these values equal other ‘IF’ functions. In this problem you are tasked with setting your ‘FALSE’ value as a SUM function of 2 values.
INCOMPLETE
MKT (##)
3
20171202122656activity_2.xlsx
Printed on 12/2/2017 at 1:27 PM
Course: Lean Logistics Operations
3
Section
1 -data
Exercise 3 Section 1 – Exercise 3
es, Autoshapes,
Linked
2 Data Trimming using formulas
Exercise 2′ Tab
INCOMPLETE
=MID(X,5,8) The mid function is similar to the LEFT and RIGHT functions discussed in exercise 1. The difference is that it will extract values from the center of a data set as opposed to
the ends allowing users to create a new data set from the center of a large data string.
=LEFT(X,5)
The left function will start from the left of a text field and return the number of characters equivalent to the value entered after the comma (i.e. 5 characters) in the
example
=RIGHT(X,5) The right function will start from the left of a text field and return the number of characters equivalent to the value entered after the comma (i.e. 5 characters) in the
example
3 =SUMIF(X:X,A1,Y:Y)
In the example to left the X:X portion is the Range – which is the array that has the values associated with your Criteria in it,
The Criteria section is a single cell that contains the value you want added, and the Sum Range section is the numeric array
you want added.
4 CUT Vs. COPY – FORMULA IMPACT
Home Menu – copy/cut options
Shows how these two functions impact functions
Exercise 3′ Tab
INCOMPLETE
Exercise 4′ Tab
INCOMPLETE
When you copy a formula and paste it, the original relative reference of the copied formulas are maintained and adjust according to the proximity of the
pasted values. For example if you copy a formula and paste it to 2 cells below the original, the formula will adjust all cell references on the formula line to 2
cells below the original formula.
5 IMAGES AND AUTOSHAPES – FREE FLOATING & AS BACKGROUND
Exercise 5′ Tab
INCOMPLETE
Images and auto shapes are useful for visual aids. Excel allows a user to insert images and shapes into the workbooks. These items can be fixed backdrops for your data, or they can
be free floating images to help you highlight your data. Auto shapes can also be used to reference and display values in your spreadsheet through a simple reference. This is valuable
for providing visual references for your data scenarios, etc. for customer/management presentations.
To insert an image select the ‘Picture’ icon from the ‘Insert’ menu. When prompted, select the ‘browse’ and select the image file you would like to insert into your spreadsheet.
Auto shapes are predefined shapes that exist within the MS Excel application. Select the ‘Shapes’ icon from the ‘Insert’ menu to bring up a list of the shapes you can load.
Imbedding objects – Images and files
Excel also allows users to imbed files into the your workbooks. Imbedded files can be images, other excel workbooks, access databases, etc. An imbedded file will appear similar to the .gif file below;
North American Political Map.gif
Open this file and save it your H drive. Insert this image as a background
into your ‘Activity 5’ tab according to the directions
If you double click on the ‘North American Political Map.gif’ object above – you will bring up a screen shot from PC Miler. Instead of sending a collection of attached files in an email to a recipient, you can create a
central discussion or analysis document (such as a word doc) and imbed your objects in a way that fits the dialogue of your presentation. Basically every MS application has this capability.
To imbed an object – select the ‘object’ icon from the ‘insert’ excel menu
If you are imbedding an object from an existing file, select ‘Create from File’ and browse for your file.
MKT (##)
3
20171202122656activity_2.xlsx
Printed on 12/2/2017 at 1:27 PM
Course: Lean Logistics Operations
3
Section
1 -data
Exercise 3 Section 1 – Exercise 3
es, Autoshapes,
Linked
Text boxes can be inserted as free floating messages in your workbooks. These posses many of the same ‘free floating’ attributes of images and auto shapes, but these are unique in
that they can enter text. The text is not adjusted by shifts (deleting) in data and will not interfere with your formula references.
Object Groupings
Auto shapes and images when inserted are free floating items. To move them, simply left mouse click (hold the button suppressed) and drag the item from it’s currently location to
the desired location. If you have multiple auto shapes or images and would prefer not to move or adjust each one individually you are provided an option to ‘group’ them. Select
two or more shapes you wish to group (holding down the cntrl button). Right click on one of the auto shapes, select group, and group within the submenu. When completed the
shapes will be group together as one single object. Grouped objects can be copied as a group if desired.
Auto shape – formulated items
Auto shapes have many potential uses. In addition to visual aides, they can be used as ‘buttons’ for macro’s that you create or for relaying the value of a cell. This comes in handy if
you are trying to show values as they relate to a map or other image. Unfortunately auto shapes cannot be programmed with advanced formulas. To make it so that an auto shape
mirrors the value of a cell, select the auto shape and in the formula box above – type =A1 or desired reference point and hit enter. No matter where you move this auto shape to it
will continue to show the results of that cell. If the values are changed the auto shape will update with the correct information.
6
6
You can change the auto shapes fill, line, and text properties by right clicking on it and selecting ‘format shape’
Hint: if you simply desire your linked value to be in a transparent box – you can select the transparency settings and line color settings to be ‘no color’. You can move your auto shape around
as desired (although it may be harder to find it to select)
Bringing images ‘Forward’ or ‘Backward’
When there is overlap between 1 or more auto shapes or images – the system will designate one item to be the most dominant item to appear in the foreground. All other items will
be placed ‘behind’ this item. To change the order of which image is in the foreground you can right click on the desired image to bring forward, hover your mouse cursor over the
‘move forward’ option, and select ‘bring forward’.
Using Auto shapes as ‘buttons’ to activate hyperlinks and Macro’s
Auto shapes can also be used to act as a ‘button’ to link the user to another location or to activate a pre-designed Macro within your workbook. Macro’s will be covered in upcoming
exercises at length. To use an auto shape in this manner simply right click on the auto shape and view the menu options.
Purchase answer to see full
attachment