Summary
Summary BUS-K 201 Final Cheat Sheet
- Course
- BUS-K 201
- Institution
- Indiana University
BUS-K 201 Final Cheat Sheet. *Essential!! * For you, at a price that's fair enough!!
[Show more]
Content preview
BUS-K 201 Final Exam Cheat Sheet Chapter 12: The Power of Excel
o Total Sales Revenue: # of Items Sold * Unit Price
o Total Variable Cost: Sales Volume * Unit Cost
o Total Fixed Cost: Sum of all Fixed Costs
o Net Profit Before Taxes: Sales Revenue – (Variable Costs + Fixed
Costs)
o Net Profit/Loss: Net Profit Before Taxes – Taxes
CONCAT, LEFT, RIGHT, LEN, MID Functions & Formulas
o Ex: Scott, Michael
First Name: =RIGHT(CELL,(LEN(CELL)-FIND(", ",CELL,1)-1))
# of Characters in First Name: =FIND(", ",CELL,1)-1
Last Name: =LEFT(CELL,FIND(", ",CELL,1)-1)
# of Character in Last Name: =LEN(CELL)-FIND(",
",CELL,1)
o “What character # is CHARACTER associated with?”
=FIND("CHARACTER",CELL,1)
o 2 Ways to Concatenate
&: =LEFT(CELL,#)&" "&RIGHT(CELL,#)
=CONCAT: =CONCAT(CELL1," ",CELL2)
DATEDIF Function
o =DATEDIF(OlderDate, NewerDate, Unit)
Unit= “D”, “M”, “Y” (“Days”, “Months”, “Years”
Drop-Down Table Creation Steps
1. Click on “Data” on the top ribbon
2. Click on “Data Validation” in the “Data Tools” section (1st column,
3rd row)
3. In “Validation Criteria”, click the drop-down button under “Allow”,
and select “List”
4. Enter your data into the “Source” box with no quotations or
spaces, only commas (A,B,C,…)
, Total # of ___ IF
o =COUNTIF(CELL,TABLE)
Averages IF
o =AVERAGEIF(TABLE1 (includes CELL),CELL,TABLE2 (gives output))
Totals IF
o =SUMIF(TABLE1 (includes CELL),CELL,TABLE2 (gives output))
VLOOKUP, HLOOKUP, MATCH, INDEX
o VLOOKUP(lookup_value, table_array, col_index_num,
[range_lookup])
lookup_value: The known or given value that you want to find
a match to in the first (leftmost) column of the data. In this
example, that would be a Supplier ID. Note that if you use a
cell reference to specify the lookup_value argument, that cell
reference should be located outside the range of cells
specified in the table_array argument.
table_array: The two-dimensional (multiple rows and columns)
range of data from which you wish to retrieve values.
col_index_num: The column number, within the table_array,
from which you want to output a value. The leftmost column in
the table_array is considered column 1, even if it does not
correspond to column A on the worksheet. In this example,
our col_index_num would be 4 since that column in our data
contains the Phone number.
range_lookup: Here you specify either TRUE or FALSE.
A range_lookup of TRUE indicates that when looking down
the leftmost column, Excel only needs to find an approximate
match to the lookup_value. In other words, it’s enough for
Excel to find the range of numbers that the lookup_value falls
within (e.g., between 1 and 10, between 11 and 20, etc.).
A range_lookup of FALSE indicates that when looking down
the leftmost column, Excel must find an exact match to
the lookup_value. If you omit this argument, Excel assumes
TRUE.
o HLOOKUP(lookup_value, table_array, row_index_num,
[range_lookup])
, lookup_value: The known or given value that you want to find
a match to in the top row of the data. In this example, that
would be an Order Quantity. Note that if you use a cell
reference to specify the lookup_value argument, that cell
reference should be located outside the range of cells in
the table_array specified in the next argument.
table_array: A two-dimensional (multiple rows and columns)
range of data from which you wish to retrieve values.
row_index_num: The row number, relative to
the table_array argument, from which you want to output a
value. The topmost row in the table_array is considered row 1,
even if it does not correspond to row 1 on the worksheet itself.
In this example, our row_index_num would be 2 since that row
in our data contains the Price.
range_lookup: Here you specify either TRUE or FALSE.
A range_lookup of TRUE indicates that when looking across
the top row, Excel only needs to find an approximate match
to the lookup_value. In other words, it’s enough for Excel to
find the range of numbers that the lookup_value falls within
(e.g., between 1 and 10, between 11 and 20, etc.).
A range_lookup of FALSE indicates that when looking across
the top row, Excel must find an exact match to
the lookup_value. If you omit this argument, Excel assumes
TRUE.
o MATCH(lookup_value, lookup_array, [match_type])
lookup_value: The known or given value that you want to find
a match to within a row or column of data. In this example,
that would be F8 or the employee’s name. Note that if you use
a cell reference to specify the lookup_value argument, that cell
reference should be located outside the range of cells specified
in the lookup_array argument.
lookup_array: The single column or row where you are looking
for the position or rank of the match to the lookup_value. In
this example, that would be B6:B27.
match_type: This will be 0, 1, or -1 depending on whether you
need Excel to find an exact match or an approximate match to
the lookup_value. A match_type of 0 indicates an exact match.
The benefits of buying summaries with Stuvia:
Guaranteed quality through customer reviews
Stuvia customers have reviewed more than 700,000 summaries. This how you know that you are buying the best documents.
Quick and easy check-out
You can quickly pay through credit card or Stuvia-credit for the summaries. There is no membership needed.
Focus on what matters
Your fellow students write the study notes themselves, which is why the documents are always reliable and up-to-date. This ensures you quickly get to the core!
Frequently asked questions
What do I get when I buy this document?
You get a PDF, available immediately after your purchase. The purchased document is accessible anytime, anywhere and indefinitely through your profile.
Satisfaction guarantee: how does it work?
Our satisfaction guarantee ensures that you always find a study document that suits you well. You fill out a form, and our customer service team takes care of the rest.
Who am I buying these notes from?
Stuvia is a marketplace, so you are not buying this document from us, but from seller anyiamgeorge19. Stuvia facilitates payment to the seller.
Will I be stuck with a subscription?
No, you only buy these notes for $10.99. You're not tied to anything after your purchase.
Can Stuvia be trusted?
4.6 stars on Google & Trustpilot (+1000 reviews)
80364 documents were sold in the last 30 days
Founded in 2010, the go-to place to buy study notes for 14 years now