NEED HELP ASAP!!!

Task 1: 16

In Area-Wise Sales sheet, total purchase value is not available for some regions and purchase dates those unknown values are indicated by NIL. Listed below are the tasks for this sheet. Use absolute and relative references carefully!

(a) Report the total sales for each of the regions in Region-wise Total sales & Averages table. Add the formula in cell H3 and drag down the formula till H6.

(b) Report average sales for each of the regions in Region-wise Total sales & Averages table (this corresponds to regular average without considering text). Add the formula in cell H9 and drag down the formula till H12.

(c) Report total sales for each of the regions where purchase value is more than $3000. For example- RN-01 has a purchase value of $2400 on September 25. This value should be ignored while computing sum. Add the formula in cell H15 and drag down the formula till H18.

(d) Report total sales for each of the regions where purchase value is between $2000 and $9000. Add the formula in cell H21 and drag down the formula till H24.

Task 2: 9

In the Dell-Purchase Week sheet, work on computing laptop costs for each of the five customers: Cust-11 to Cust-15. You will need logical rules to work on laptop cost. Apply those rules to customers preferences (captured in light yellow colored table by sales executive) and lookup for each components cost from Dell Unit Cost sheet. Your logical

formula would be a combination of IF and VLOOKUP. Using these rules, work towards identify what each customer is paying for his/her version of laptop. The formula should be written only once in cell B14 and then that formula should be dragged horizontally and vertically in the green-colored table below. This will require you to use appropriate type of referencing for the excel cells in the formula (just to remind there are three types of cell referencing: relative, absolute and mixed referencing).

For example: Cust-11 wants basic laptop + optical mouse + video card+extended warranty. Write rules for him in green colored table below as: If there is 1 for basic laptop, then cost after lookup from Dell Unit cost sheet is $1067 else cost is $0; if upgrade to 2GB RAM is 1, then cost is $130 else cost is zero... and so on applying such rules will let you know much Cust-11 is paying for his version of laptop. Repeat these rules for other customers.

Dell Purchases -Week Area-Wise Sales Dell-Unit Cost Dell Purchases -Week \begin{tabular}{|l|c|} \multicolumn{1}{c|}{ Item Title } & Cost \\ \hline \multicolumn{1}{|c|}{ Basic Dell Computer @ 1067 } & $1,067.00 \\ \cline { 2 } Additional Item List: & \\ \hline Upgrade to 2 GB RAM & $130.00 \\ \hline Optical Mouse & $10.00 \\ \hline Upgrade to 19" flat panel monitor & $190.00 \\ \hline Video card & $40.00 \\ \hline Linksys Wireless Router & $82.00 \\ \hline Hard Disk Drive Upgrade to 500GB & $220.00 \\ \hline Shipping and Handling & $75.00 \\ \hline Extended Warranty & $95.00 \\ \hline \end{tabular} Area-Wise Sales Dell-Unit Cost Dell Purchases -Week rea-Wise Sales Dell-Unit Cost Dell Purchases -Week LAPTOF COST COMPUATION TABE Dell Purchases -Week Area-Wise Sales Dell-Unit Cost Dell Purchases -Week \begin{tabular}{|l|c|} \multicolumn{1}{c|}{ Item Title } & Cost \\ \hline \multicolumn{1}{|c|}{ Basic Dell Computer @ 1067 } & $1,067.00 \\ \cline { 2 } Additional Item List: & \\ \hline Upgrade to 2 GB RAM & $130.00 \\ \hline Optical Mouse & $10.00 \\ \hline Upgrade to 19" flat panel monitor & $190.00 \\ \hline Video card & $40.00 \\ \hline Linksys Wireless Router & $82.00 \\ \hline Hard Disk Drive Upgrade to 500GB & $220.00 \\ \hline Shipping and Handling & $75.00 \\ \hline Extended Warranty & $95.00 \\ \hline \end{tabular} Area-Wise Sales Dell-Unit Cost Dell Purchases -Week rea-Wise Sales Dell-Unit Cost Dell Purchases -Week LAPTOF COST COMPUATION TABE