- Use Data Validation to allow users to select Item Numbers from an alphabetized list of the items on the Inventory worksheet in the Item Column of the Customer Order range of cells.
- Using VLOOKUP, have the form automatically complete the “Name” and “Price” values once the Item Number is selected in column B in the Customer Order range of cells. For rows without an item selected, make sure the name and price display blank.
- In the Charge column of the Customer Order range of cells, calculate the extended price of each item row. This cell must appear blank if there are no items ordered in a row but reflect the total price for the row if an item is ordered.
- In the cell to the right of the text “Subtotal” calculate the total charge for all items ordered.
- In the cell to the right of the text “Discount” calculate the discount if the minimum order exceeds the stated value under “Min. Order for Discount” and $0 if the order does not meet the discount threshold. Change the value in the cell for Min. Order for Discount from $500 to $200. Change the discount from 4% to 5%. Enter ordered items in the order form to test the formulas and functions used.
- Calculate the price after discount in the cell to the right of the text “After Discount”.
- Calculate the sales tax in the cell to the right of the text “Sales Tax” using a cell reference to the cell to the right of your calculation.
- In the cell to the right of the text “Shipping” in the lower part of the form, use a combination of IF and VLOOKUP to calculate the shipping cost based on the value selected in the cell to the right of the text “Shipping” in the upper part of the form (originally cell C15 – but this may have changed depending on how you have added information). Account for free shipping if the price after discount is greater than the free shipping threshold. Be certain to reference the cell where you entered the free shipping threshold of $300 and the range of cells where the Shipping Option prices are contained in your formulas and functions.
- In the cell to the right of “TOTAL” calculate the total price the customer will pay for product, tax, and shipping after discounts.
including advanced functions like IF, VLOOKUP, Data Validation, and determining when to use a function and when to use formulas. If it’s possible to use a cell reference for a value, you MUST use a cell reference for a value.
How our paper writing service works
It's very simple!
-
Fill out the order form
Complete the order form by providing as much information as possible, and then click the submit button.
-
Choose writer
Select your preferred writer for the project, or let us assign the best writer for you.
-
Add funds
Allocate funds to your wallet. You can release these funds to the writer incrementally, after each section is completed and meets your expected quality.
-
Ready
Download the finished work. Review the paper and request free edits if needed. Optionally, rate the writer and leave a review.