MIS 180- EXCEL
BOOLEAN FUNCTIONS 3 Calculate "Down OK?" (cell J12) using a boolean formula that returns TRUE if the "Down Payment" (cell F12) exceeds 10% of the "Loan Amount" (cell D12). Copy your formula down to complete the "Down OK?" column.
"="/ "click cell"/">click cell * percentage"
BOOLEAN FUNCTION 3 Calculate "PMT OK?" (cell I12) using a boolean formula that returns TRUE if the "PMT/Income" (cell E12) ratio is below 43%. Copy your formula down to complete the "PMT OK?" column.
"="> "Click cell"> "<.43" less than
Construct a formula in cell E11 to calculate the total amount for transaction 578. Be sure to appropriately reference the transaction amount in cell C11 and the sales tax amount in cell D11 so that you can reuse your formula to calculate the total for the remaining transactions. 21.25 FORMULAS
"=">"ref cell> "*">ref cell> "+"> ref cell> enter
Construct a formula in cell D11 to calculate the sales tax amount for transaction 578. Be sure to appropriately reference the transaction amount in cell C11 and the sales tax rate in cell C8 so that your formula can be reused for the remaining transations. 21.25 FORMULAS
"=">"ref cell> "*">ref cell> enter
BOOLEAN FUN 3 Calculate "Approved?" (cell L12) using the AND function. Return TRUE if "PMT OK?" (cell I12), "Down OK?" (cell J12), and "Score OK?" (cell K12) all evaluate as TRUE (notice no quotes around the word TRUE since it is a Boolean result and not text). Copy your function down to complete the "Approved?" column.
"=AND(cell="TRUE", cell="TRUE....)
COUNTIF FUNCTION 3 Calculate the "# of Transactions" (cell G9) using the COUNTIF function. The range for the function should reference the "Location" values on the "Transaction Details" table (range C9:C73) and the criteria should reference the appropriate "Location" value on the "Sales Summary" table (cell F9). Copy your formula down to complete the "# of Transactions" column. Be careful to use the correct relative and absolute cell referen
"=COUNTIF(range of stores abs val,critera=location on sum table)
IF FUNCTION 3 Calculate "PMT OK?" (cell I12) using an IF function that returns "Yes" if the "PMT/Income" (cell E12) ratio is below 43% and "No" otherwise. Copy your formula down to complete the "PMT OK?" column
"=IF(E12cell<.43, "Yes","No")
IF FUNCTION 3 Calculate "Down OK?" (cell K12) using an IF function that returns "Yes" if the "Down Payment" (cell F12) exceeds 10% of the "Loan Amount" (cell D12) or if the "Down Payment" exceeds 5% of the "Loan Amount" and the "PMT/Income" ratio (cell E12) is less than 40%. Otherwise, the if function should return "No". Copy your formula down to complete the "Down OK?" column.
"=IF(cell>cell*percent, "yes", IF(and(Down pay>Cell *percent, Incoe rat<.40), "yes","no"))
IF FUNCTION 3 Calculate "Discount?" (cell L12) using the IF function with an OR function as part of the criteria argument. Return "Yes" if either "Savings Account?" (cell G12) or "AutoPay?" (cell H12) are listed as "Yes". Otherwise, return "Suggest AutoPay". Copy your function down to complete the "Discount?" column.
"=If(Or(Sav acc="yes", Autopay="yes"), "yes","suggest autopay"
BOOLEAN FUNCTION 3 Calculate "Score OK?" (cell K12) using the NOT function. Return TRUE if the "Credit Score" (cell C12) is not less than the "Threshold Credit Score" (cell F9). Copy your function down to complete the "Score OK?" column on the table. Be sure to appropriately use relative and absolute cell references.
"=NOT(Click cell<Click cell(abs ref)
BOOLEAN FUN 3 Calculate "Discount?" (cell M12) using the OR function. Return TRUE if either "Savings Account?" (cell G12) or "AutoPay?" (cell H12) are listed as "YES". Copy your function down to complete the "Discount?" column.
"=OR(cell="yes",cell="yes")
COUNTIF FUNCTION 3 Calculate "Total Sales" (cell H9) using the SUMIF function. The range for the function should reference the "Location" values on the "Transaction Details" table (range C9:C73), the criteria should reference the appropriate "Location" value on the "Sales Summary" table (cell F9), and the sum_range should reference the "Sale Amount" values on the "Transaction Details" table (range D9:D73). Copy your formula down to complete the "Total Sales" column. Be careful to use the correct relative and absolute cell references.
"=SUMif(range is location,critera (specific), sum range sale amt abs ref)
Use the SUM function to calculate the "Grand Total" in for all transactions in cell E24. 21.25 FORMULAS
"=Sum">"E11:E23"
REFERENCE FUN 3 Calculate "Risk Level" (cell E9) using the VLOOKUP function. The lookup_value is the "Credit Score" (cell C9). Table_array refers to the "Car Loan Interest Rates" table (range I10:K17). The col_index_num is 2 (the second column of the reference table). Finally, [range_lookup] is "TRUE" (an approximate match is OK). Copy your formula down to complete the "Risk Level" column. Be careful to use correct relative and absolute cell references.
"=VLOOKUP(Single cell credit score, table array reference location (I10-K17 abs ref, risk cat is 2nd column (2), "TRUE")
IF FUNCTION 3 Calculate "Score OK?" (cell J12) using the IF function. Return "Yes" if the "Credit Score" (cell C12) is at least (>=) the "Threshold Credit Score" (cell F9) otherwise return "No" Copy your function down to complete the "Score OK?" column on the table. Be sure to appropriately use relative and absolute cell references.
"IF(C12cell>=Thresold abs ref, "yes","no")
IF FUNCTION 3 Calculate "Approved?" (cell M12) using the IF function with an AND function as part of the criteria. Return "Yes" if "PMT OK?" (cell I12), "Score OK?" (cell J12), and "Down OK?" (cell K12) are "Yes". Return "Loan too large" if "PMT OK?" is "No". Return "Score too low" if "Score OK?" is "No". Otherwise, return "Down too low". Notice that if "PMT OK?" is "No" only "Loan too large" is displayed regardless of the status of "Score OK?" and "Down OK?" See the reading for details on why. Copy your function down to complete the "Approved?" column.
"if(and(PMT ok="yes", scoreok="yes",down ok="yes"),"yes", if(payment="no","loan too large",if(Score ok= "no", "score too low", "down too low")
TEXT FUNCTIONS 4 SEARCH
(find_text,within_text, [start_num]) - returns the position of a specific character, word, or phrase within a block of text
COUNTIF FUNCTION 3 Calculate "Average Sales" (cell I9) using the AVERAGEIF function. The range for the function should reference the "Location" values on the "Transaction Details" table (range C9:C73), the criteria should reference the appropriate "Location" value on the "Sales Summary" table (cell F9), and the average_range should reference the "Sale Amount" values on the "Transaction Details" table (range D9:D73). Copy your formula down to complete the "Average Sales" column. Be careful to use the correct relative and absolute cell references.
=AVERAGEIF(range values, critera location, average range location of all transactions)
REF FUN 3 Calculate "Discount" (cell G9) using the HLOOKUP function. The lookup_value is the "Zip Code" (cell D9). Table_array refers to the "Zip Code Discount" table (range N9:P10). The row_index_num is 2 (the second row of the reference table). Finally, [range_lookup] is "False" (we need an exact match). Copy your formula down to complete the "Discount" column. Be careful to use correct relative and absolute cell references. Note: a result of #N/A means that the customer does not get a discount (a match for that customer's zip code was not found on the reference table).
=Hlookup(zipcode single, table array abs ref, row index =2 2nd row, exact match, "false")
NPER function
=NPER (rate,pmt,pv [,fv][,type])
REF FUN 3 Calculate "Rate Charged" (cell F9) using the VLOOKUP function. The lookup_value is the "Credit Score" (cell C9). Table_array refers to the "Car Loan Interest Rates" table (range I10:K17). The col_index_num is 3 (the third column of the reference table). Finally, [range_lookup] is "TRUE" (an approximate match is OK). Copy your formula down to complete the "Rate Charged" column. Be careful to use correct relative and absolute cell references.
=Vlookup( value credit score single, table array reference abs ref, interest rate=3rd column (3), "TRUE")
Set the print area of the worksheet to include only the area of the data table (Range B2:J50) 1.1-1.5
File>Highlight cells>Print Area
Set column B as "Columns to repeat at left" page titles 1.1-1.5
File>Page Setup>Sheet>Highlight cells> Columns to repeat at left
Use the AVERAGE function to calculate the average salary for the teams in 1990. 21.25 STATS
Formulas> Insert Function> AVERAGE
Use the COUNT function to calculate the number of MLB teams in 1990 (range G10:G39). STATS 21.25
Formulas> insert function> count
Use the MAX function to determine the maximum team salary amount in 1990. 21.25 STATS
Formulas>Insert Function>MAX
Use the MIN function to determine the minimum team salary amount in 2010. 2125 STATS
Formulas>Insert Function>MIN
Use the SUM function to calculate the total amount of salaries paid in 1990. STATS 21.25
Formulas>Insert Function>SUM
Use the COUNTA function to calculate the number of MLB teams in 2010 (use range F10:F39). STATS 21.25
Formulas>Insert function> COunta
In the same sort, first sort the table on the "World Records Sorted" worksheet by Country in ascending order (A to Z) then sort the table by 'Record Date' in descending order (Newest to Oldest). 32 8 11.12
Hom Tab>Sort & Filter
Place a dotted line border (the option directly under "None" in the "Border" tab of the "Format Cells" dialog window) under each subsequent row in the table. 1.1-1.5
Home Tab> Highlight cells>Format> Format Cells>Border> "None">Dotted line
Change the format of the date range (G4:G73) to "Short Date" 1.1-1.5
Home Tab> Long "general" tab> short date
Merge and center cell G2 across columns G:J 1.1-1.5
Home Tab> Right button with lines/blue arrow> Merge & Center
Filter the table on the "World Records Sorted" worksheet to display only athletes whose "Nationality" listed as "Kenya" or "Jamaica" 1.1-1.2
Home Tab>Sort & Tab> Filter> Arrow should show up on cell
Center the table headers (range B3:J3) horizontally 1.1-1.5
Home tab> Highlight cells> Middle lines
Make the table headers (range B3:J3) bold 1.1-1.5
Home tab>Bolded B
Place a "Thick Bottom Border" (from the border drop-down menu) under the data labels (range B3:J3) 1.1-1.5
Home tab>Box with thick line
Fill rows 2 and 3 of the table (range B2:J3) using any color (not "No Fill") from the "Fill Color" drop-down menu. 1.1-1.5
Home tab>Fill icon
Notice that both the "Place" and "Country" fields display the country where the record was established. Hide column I ("Place") to avoid redundency in the table 11-1.5
Home tab>Format>Hide & UnHide
Insert a new column between columns B and C. Label the new column (cell C3) "Record ID" 1.1-1.5
Home tab>Insert green button>insert column or row sheet
You are interested in purchasing a home. What will your monthly payment be if you take out a $175,000 mortgage for 30 years (360 months) at 4.25% interest? (Please reference the loan information in the "Task 1 Data" cells in your functions.) FINANCE 21.25
Loan amt- 1750,000 Interest rate- 4.25% Number of Payment- 360 Monthly Payments-?? PMT FUNCTION: "=PMT(Interest rate/12,number of payments,loan amt)"
You are interested in purchasing a home. You have been quoted monthly payments of $950 for a 30 year mortgage. Your original loan amount is $212,000. What is the interest rate you will pay on the loan? (Please reference the loan information in the "Task 3 & 4 Data" cells as the arguments for your functions.) What is the effective annual rate of the loan you worked with in task 3? (Please reference the loan information in the "Task 3 & 4 Data" cells as the arguments for your functions.) 21.25 FINANCE
Loan amt- 212,000 Interest rate- ??? Number of Payment- 360 Monthly Payments-950 Nominal INTEREST RATE: "=RATE(Number of payments, Monthly Payment Amount, Present Value((Loan AMT))>enter. multiply by 12 EFFECTIVE IR: "=EFFECT(Nominal interest rate, 12 (Number of periods)>enter
You are interested in purchasing a home. You can afford $1200 a month as a mortgage payment. How much can you pay for a home assuming a 30 year (360 months) loan at 4.25% interest? (Please reference the loan information in the "Task 2 Data" cells as the arguments for your function.) 21.25 FINANCE
Monthly Payment- 1200 Interest rate- 4.25 Number of payments-360 Loan Amt??? PV FUNCTION =PV(Interest rate/12, Number of periods, Payment value)
You are interested in saving for a trip when you graduate in three years. You can save $75 each of the next 36 months and earn 2.75% interest on your money. How much money will you have in your savings account in 36 months for your trip? (Please reference the loan information in the "Task 5 Data" cells as the arguments for your function.) 21.25 FINANCE
Monthly Savings AMT: -75 Interest Rate- 2.75% Number of Months-36 Future acc bal=?? FV (Interest/12, Number of Months, Monthly Savings)>enter> total multiply AMT
Set the left and right print margins of the worksheet to be .5 inches 1.1-1.5
Page Layout> Print Margins> Custom Print
Use the "Fill" feature of Excel to insert the numbers 1 to 47 in cells C4:C50. 1.1-1.5
Type #1, #2> Home tab> Blue down arrow> Series> Autofill
MID(text,start_num,num_chars) RIGHT(text,[num_chars]) UPPER (text) LOWER (text) PROPER (text) CONCATENATE (text1,[text2],...) SUBSTITUTE (text,old_text,new_text,[instance_num])
returns a specified number of character from the middle of a block of text returns a specified number of characters starting from the end of a block of text converts a block of text to all upper-case characters converts a block of text to all lower-case characters converts a block of text to title-case (the first letter of each new word is capitalized) combines blocks of text - replaces specified characters, words, or phrases within a block of text with new characters, words, or phrases
TEXT FUNCTIONS 4 LEFT (text,[num_chars])
returns a specified number of characters starting from the beginning of a block of text
TEXT FUNCTIONS 4 Len
returns the length, in number of characters, of a block of text
