Excel Functions Test [PDF]

Zitiervorschau

INSTRUCTIONS: On the tabs of this excel you will find a number of different tasks that require you to use your knowledge of Excel functions to solve. This test is simply meant to give us an idea of what you know about Excel as it is often used in our line of work. Please complete as many tests as you can and solve them using the most effective function. In many situations it would be possible to complete the task by manually typing. DO NOT use this method, as the test is meant to gauge your ability to use excel functions.

Name Jane Juan Mary John

Date

Average INSTRUCTIONS: Use an excel function to average the above amounts.

Amount Paid 8/2/2012 7/16/2000 2/1/2001 3/23/2011

$50.00 $75.00 $60.00 $72.00

Name Wellington Texas Instruments BAE Lockheed Martin

Instructions: Apply a filter to this page so that only those amounts that are $100 or more are shown.

Date Paid

Amount 5/18/2012 4/1/2013 8/20/2011 9/15/2012

$150.00 $75.00 $20.00 $100.00

State Virginia Texas New Mexico California

Second Word pilot driver fall

First Word jet car free

Completed Phrase

tractor

tractor trailer

INSTRUCTIONS: Use a formula to combine the above terms so that they form a phrase as in the example: Example: trailer

Name Apple Apricot Avocado Banana Breadfruit Apple Blackberry Blackcurrant Blueberry Boysenberry Currant Cherry Cherimoya Blueberry Cloudberry Coconut INSTRUCTIONS:

Using a single formula, detect all of the duplicates in the above list. If the term has already appeared in the list, then your formula should display it on the same row, if the term has not already appeared on the list then your formula should display N/A.

Detection

Name Anise Basil Caraway Cilantro Chamomile Dill Basil Lavender Lemon Grass Marjoram Anise Parsley Basil Sage Anise INSTRUCTIONS:

Create a Pivot table using the above data. Some spices appear more than once in the list, so on your table, make sure the total amount spent on each spice is displayed.

Price $0.50 $0.25 $1.00 $2.00 $2.20 $2.00 $0.75 $0.80 $0.99 $0.44 $0.41 $0.84 $0.96 $0.25 $1.00

ARM Loan Information Type of Loan: Home Value Enter Loan Amount Here: Term (years) Starting interest rate Current rate after fixed term fin Extra Cost E=

INSTRUCTIONS: Use an Excel formula to check if C2 cell is a 'FHA' Loan. Then execute the following steps: In cell E3 - If the 'Type of Loan' = FHA, multiply the value in C4 by 0.0175, if not return 0. In cell E4 - If the 'Type of Loan' = FHA, multiply the value in C4 by 1.0175, if it is not an FHA Loan, then return the value in C4. In cell E5 - If the 'Type of Loan' = FHA, multiply the value in C3 by .78, if not return 0.

FHA $210,527 UFMIP $200,000 Loan Amount + UFMIP 30 78% Equity 2.7500% 3.3750% $0.00

Character Strings 3582473160X475 48129047X773405 89017835X35 520853124X9972

Result

INSTRUCTIONS:

Create a formula that returns only the characters that appear after "X" for the given character strings. The same formula must work for the whole column! Example of how it should look like after you apply your formula: 765892X329 752238X44

329 44