Excel Formulas

B6 - create a formula that calculates the coupon dollar value for this customer. Be sure to include both the base $25 and the percentage of the rental cost in B3.

=(B3*B5)+25

Create a formula in cell C6 that calculates the sales price for the customer on row 6. Your formula must use the correct combination of relative and absolute cell references so that it can be copied correctly down to rows 7 through 70.

=(B6*$B$2)

First, add $10 to the coupon amount. Then double that dollar value. Please create minimum purchase amount formulae in H2 through H100 based on the rules above. The boss knows that there will be a minimum purchase of $20 on all the blank rows. No problem. After she pastes in the data for the 100 lucky customers who get coupons, your formulae should display the correct results.

=(G2+10)*2

Create a formula in F6 that calculates the bonus paid to Ruth for the olive oil and vinegar that she sold, represented on row 6. Your result should be $11.31

=C6*$H$2

Now create a formula in G6 to calculate her Pantry Item bonus ... which should be $0.88

=D6*$H$3

Create a formula that correctly computes the number of weeks that cabin was rented.

=DATEDIF(D2, E2, "d")/7

Guitar Universe pays a bonus on any sale over $200. The current bonus percentage rate is in cell 11. If a sale is over $200, you just multiply the sale amount times the bonus percentage. If the sale is $200 or less, then the bonus is $0. Step 1 - Create a formula in cell F4 that calculates the bonus for that row. Warning: management likes to change the commission rate fairly often, so do not just type the current rate into your formula as a constant. Step 2 - Now copy that formula into the remaining rows (cells F5:F47). Step 3 - Format the bonus amount cells to display with exactly 2 decimal places.

=IF(E4>200,0.0225*E4,0)

Create a formula in E10 that displays the total rental cost, based on the number of weeks entered in B6 and the actual rental rate you calculated in D10.

=IFERROR((B6*D10),"")

Your task is to create formulas in G2:H60 of the Current Rates sheet that will analyze the content of B2:B61 and display either a "Yes" or nothing ("") to tell the user whether or not that cabin has a particular feature (fireplace for column G and hot tub for column H). Hint: this will require an IF, a SEARCH or FIND, and an IFERROR function.

=IFERROR(IF(SEARCH("Fireplace",B2),"Yes",""),"") =IFERROR(IF(SEARCH("hot tub",B3),"Yes",""),"")

Create a formula in D10 that meets all of the following requirements: • If B5 contains the letter O then display the off-season rate for this cabin. • If B5 contains the letter S then display the standard rate for this cabin. • If B5 is blank or contains anything other than O or S, then the cell should be blank.

=IFERROR(IFS(B5="O", B10, B5="S", C10),"")

Add a new column to the right of the Cabin Rentals Sheet; it should be column P. The column heading in P1 is: Regional Manager. Each of the 9000+ data rows in column P should list the correct regional manager's name. Use the Excel logical functions covered in week 6. Here are the rules your function must use: if the state is Florida, the manager is Clint. If the state is Georgia or Alabama, the manager is Peggy. For all other states, the manager is Robin.

=IFERROR(IFS(M2="Florida", "Clint", M2="Georgia","Peggy",M2="Alabama","Peggy"),"Robin")

Create a function in cell B1 that will extract some text from the middle of the Constitution, beginning with character 2398 and extracting 7 characters.

=MID(E1, 2398,7)

Create a formula in cell C72 that displays the total sales for all Gizmo sales in C6 through C70.

=SUM(C6:C70)

Create a formula in E6 that calculates the total sales amount for that row (98 dollars). Then copy that formula down to the rest of the sales data rows for this month.

=SUM(C6:D6)

Create a formula in cell 13 that calculates the total bonus paid for all sales. Format it to display with exactly 2 decimal places. Question: What is the total bonus amount?

=SUM(F4:F47)

Next, create a formula in H6 to calculate her total bonus payment for row 6.

=SUM(F6:G6)

B5 - create a formula that looks up the correct free food percentage rate based on the rental cost in B3. Your formula must use an XLOOKUP or a VLOOKUP function.

=VLOOKUP(B3,E3:F7,2,TRUE)

Create a formula that will lookup information about whatever cabin number is in B4 and display the appropriate information in cells A10:C10. See the example screen capture above. NOTE: If B4 is blank, or if an invalid cabin number has been entered into B4, then your formula should display "Please enter a valid cabin number" in A10.

=XLOOKUP(B4, 'Current Rates'!A2:A60,'Current Rates'!C2:D60,"Please enter a valid cabin number",0)

Please produce a pivot table with... • Each payment type (AmEx, etc.) on its own row. • One column for each year of data. • Total rental revenue calculated in the body of the pivot table. • Format the total revenue as follows: no dollar sign, two decimal places, and use commas as thousands separators. • Rename the sheet to be: "Pivot 1 - Payment Types"

Copy all data and paste into new sheet > Create pivot table > Date rental ends (columns) > Payment Type (rows) > Payment Type (values)

They'd like you to do an analysis by State (one state per row on the pivot table) that answers the following questions: • How many rentals from that state? • Sort the results in order of descending number of rentals (i.e. the state with the most rentals should be at the top and the fewest at the bottom). • Rename the sheet to be: "Pivot 2 - Geographic Analysis"

Copy all data and paste into new sheet > Create pivot table > State (rows) > State (values/count setting) > sort largest to smallest

Get rid of all duplicate entries.

Data > Remove Duplicates > Select Columns

Setup your pivot table so that.. The rows show the numeric scores, grouped in 25-point blocks. So you should have rows for 25-50, 50-75, and 75-100 points. The columns should display the class (Freshman, Sophomore, Junior). The body should show how many students meet those criteria.

Insert pivot table > Class (columns) > Grade (rows) > Count of Grade (values) > Analyze PivotTable > Group Grade by 25 starting from 25 and ending at 100.

On this pivot table, your goal is to determine the average numeric score for each class. Your pivot table should have three rows - for freshmen, sophomores, and juniors.

Insert pivot table > Class (rows) > Average of Grade (Values)

Create a pivot table using the grades data in the spreadsheet. You want to know how many students got each letter grade. Your pivot table should have 5 rows - one for each letter grade A through E.

Insert pivot table > Letter (rows) > Class (columns) > Count of Letter (values)

Validation rules.

Select Data >Data Validation. In the Settings tab, under Allow, select an option. Under Data, select a condition. Set the other required values based on what you chose for Allow and Data. Select the Input Message tab and customize a message users will see when entering data. Select the Show input message when cell is selected checkbox to display the message when the user selects or hovers over the selected cell(s). Select the Error Alert tab to customize the error message and to choose a Style. Select OK.

Freeze panes.

Select View > Freeze Panes > Freeze Panes

Do a multilevel sort, first by A-Z then by amount (largest to smallest).

Sort Box > Add Levels

Which of the text functions covered this week will discard excess blanks?

TRIM()

You want a spreadsheet with your sales people's names listed across the top row. Your assistant goofed and typed them down the first column. What do you do to fix this?

Use Paste Special: Transpose

Split into first and last names

first =LEFT(A2,FIND(" ",A2,1)-1) last =RIGHT(A2,LEN(A2)-FIND(" ",A2,1))