Excel Problems
jaimezcla
Assignmentpart1-Ifstatements.xlsmUseForData
| Client ID | Region | Week1 | Week2 | Week3 | Week4 | Total Purchases | Region Bonus | Validate | Travel Adjustment | Improvement | North Bonus | South Bonus | Result |
| 100366 | Northeast | $6,507.50 | $0.00 | $8,036.00 | $0.00 | $14,543.50 | |||||||
| 100476 | Northeast | $4,524.37 | $0.00 | $7,298.75 | $3,115.99 | $14,939.12 | |||||||
| 100842 | Southeast | $366.66 | $751.69 | $751.77 | $751.73 | $2,621.84 | |||||||
| 100846 | Northwest | $1,357.72 | $0.00 | $0.00 | $1,418.60 | $2,776.32 | |||||||
| 100918 | Northeast | $6,773.65 | $0.00 | $0.00 | $8,639.91 | $15,413.56 | |||||||
| 101030 | Northeast | $4,106.23 | $0.00 | $9,613.68 | $1,205.38 | $14,925.30 | |||||||
| 101142 | Southwest | $5,726.60 | $0.00 | $0.00 | $16,400.00 | $22,126.60 | |||||||
| 101166 | Southeast | $452.92 | $0.00 | $2,460.00 | $0.00 | $2,912.92 | |||||||
| 101308 | Northwest | $412.00 | $675.68 | $1,013.52 | $675.68 | $2,776.88 | |||||||
| 101336 | Northwest | $414.62 | $1,996.05 | $0.00 | $300.00 | $2,710.68 | |||||||
| 101408 | Southeast | $1,002.57 | $0.00 | $0.00 | $1,933.96 | $2,936.53 | |||||||
| 101940 | Southeast | $408.00 | $0.00 | $0.00 | $2,851.22 | $3,259.22 | |||||||
| 102272 | Southwest | $17,933.40 | $787.00 | $4,469.39 | $2,366.45 | $25,556.24 | |||||||
| 102476 | Northwest | $2,212.03 | $307.50 | $300.00 | $0.00 | $2,819.53 | |||||||
| 102504 | Northwest | $685.00 | $0.00 | $2,050.00 | $0.00 | $2,735.00 | |||||||
| 102890 | Southeast | $806.26 | $0.00 | $692.29 | $1,666.85 | $3,165.40 | |||||||
| 103616 | Southeast | $493.20 | $0.00 | $984.00 | $1,476.00 | $2,953.20 | |||||||
| 103992 | Northeast | $11,979.01 | $0.00 | $1,042.22 | $1,455.50 | $14,476.73 | |||||||
| 104184 | Northeast | $11,980.36 | $0.00 | $300.00 | $2,660.90 | $14,941.26 | |||||||
| 104378 | Southwest | $21,680.59 | $0.00 | $1,699.93 | $0.00 | $23,380.51 | |||||||
| 104586 | Southwest | $8,069.30 | $14,354.70 | $0.00 | $0.00 | $22,424.00 | |||||||
| 105272 | Northwest | $411.78 | $712.24 | $909.90 | $712.07 | $2,746.00 | |||||||
| 105412 | Southwest | $4,686.32 | $0.00 | $463.89 | $21,426.69 | $26,576.90 | |||||||
| 105566 | Southwest | $3,532.35 | $18,094.30 | $1,085.52 | $4,398.60 | $27,110.77 | |||||||
| 105744 | Southwest | $3,909.41 | $18,873.09 | $0.00 | $2,009.00 | $24,791.50 | |||||||
| 105758 | Southwest | $9,038.92 | $0.00 | $0.00 | $15,291.36 | $24,330.28 | |||||||
| 105792 | Northeast | $5,575.63 | $0.00 | $9,343.70 | $300.00 | $15,219.32 | |||||||
| 105930 | Northeast | $597.32 | $4,182.00 | $0.00 | $10,713.30 | $15,492.62 | |||||||
| 106044 | Northeast | $1,480.09 | $0.00 | $0.00 | $13,635.03 | $15,115.13 | |||||||
| 106282 | Southwest | $5,145.96 | $12,681.52 | $8,419.74 | $0.00 | $26,247.22 | |||||||
| 106394 | Northwest | $480.87 | $412.51 | $388.93 | $1,396.35 | $2,678.67 | |||||||
| 106732 | Southwest | $4,035.46 | $1,476.00 | $2,955.28 | $15,331.84 | $23,798.57 | |||||||
| 106766 | Northeast | $6,193.75 | $0.00 | $8,683.23 | $0.00 | $14,876.97 | |||||||
| 106884 | Southeast | $1,223.41 | $1,763.00 | $0.00 | $0.00 | $2,986.41 | |||||||
| 107118 | Southeast | $438.40 | $2,460.00 | $0.00 | $0.00 | $2,898.40 | |||||||
| 107412 | Southeast | $2,355.97 | $300.00 | $0.00 | $0.00 | $2,655.97 | |||||||
| 107466 | Southeast | $1,600.68 | $0.00 | $0.00 | $1,681.08 | $3,281.76 | |||||||
| 107770 | Southeast | $400.80 | $821.64 | $821.64 | $821.64 | $2,865.72 | |||||||
| 108420 | Southeast | $712.40 | $0.00 | $1,739.24 | $300.00 | $2,751.64 | |||||||
| 108478 | Southeast | $2,452.61 | $0.00 | $0.00 | $695.68 | $3,148.29 | |||||||
| 108688 | Southeast | $2,650.23 | $0.00 | $0.00 | $300.00 | $2,950.23 | |||||||
| 109088 | Northeast | $5,809.90 | $8,437.80 | $0.00 | $300.00 | $14,547.70 | |||||||
| 109230 | Northwest | $1,620.84 | $0.00 | $300.00 | $695.63 | $2,616.47 | |||||||
| 109598 | Southeast | $369.90 | $0.00 | $1,312.00 | $984.00 | $2,665.90 | |||||||
| 109628 | Northeast | $10,412.00 | $4,472.28 | $0.00 | $0.00 | $14,884.28 | |||||||
| 110068 | Southwest | $23,082.51 | $2,166.46 | $1,789.30 | $0.00 | $27,038.27 | |||||||
| 110120 | Northwest | $2,316.67 | $300.00 | $0.00 | $0.00 | $2,616.67 | |||||||
| 110266 | Northwest | $1,333.96 | $0.00 | $0.00 | $1,400.80 | $2,734.75 | |||||||
| 110408 | Southwest | $6,052.24 | $0.00 | $10,904.98 | $8,388.45 | $25,345.67 | |||||||
| 110424 | Northeast | $6,449.16 | $0.00 | $415.67 | $7,793.87 | $14,658.69 | |||||||
| 110596 | Southwest | $18,031.19 | $2,248.79 | $0.00 | $1,858.16 | $22,138.14 | |||||||
| 110600 | Southwest | $387.30 | $11,480.00 | $15,170.00 | $0.00 | $27,037.30 | |||||||
| 110656 | Southwest | $654.64 | $6,413.79 | $9,184.00 | $6,284.48 | $22,536.92 | |||||||
| 111066 | Northwest | $307.43 | $0.00 | $1,380.88 | $1,087.32 | $2,775.63 | |||||||
| 111230 | Southwest | $1,353.56 | $1,897.30 | $16,257.94 | $2,865.19 | $22,374.00 | |||||||
| 111294 | Southwest | $3,376.94 | $0.00 | $0.00 | $22,130.65 | $25,507.59 | |||||||
| 111690 | Northeast | $1,839.02 | $10,092.56 | $1,142.26 | $1,441.56 | $14,515.40 | |||||||
| 111722 | Northeast | $4,913.74 | $2,554.30 | $0.00 | $7,060.20 | $14,528.24 | |||||||
| 111822 | Southeast | $1,634.42 | $0.00 | $1,193.92 | $0.00 | $2,828.34 | |||||||
| 111826 | Southeast | $1,430.80 | $0.00 | $300.00 | $1,096.00 | $2,826.81 | |||||||
| 111982 | Southeast | $410.43 | $819.97 | $819.97 | $819.97 | $2,870.33 | |||||||
| 112032 | Northeast | $6,148.88 | $0.00 | $717.53 | $8,718.67 | $15,585.08 | |||||||
| 112052 | Southwest | $6,507.47 | $0.00 | $0.00 | $16,658.11 | $23,165.58 | |||||||
| 112228 | Southwest | $15,307.73 | $3,812.93 | $3,670.98 | $2,447.36 | $25,239.00 | |||||||
| 112280 | Northwest | $405.36 | $830.85 | $830.88 | $830.91 | $2,898.00 | |||||||
| 112596 | Northeast | $1,744.01 | $0.00 | $12,005.62 | $1,435.00 | $15,184.63 | |||||||
| 112684 | Southeast | $516.57 | $2,191.73 | $0.00 | $0.00 | $2,708.30 | |||||||
| 112904 | Southeast | $1,302.25 | $0.00 | $0.00 | $1,582.69 | $2,884.95 | |||||||
| 112916 | Southwest | $1,691.95 | $0.00 | $0.00 | $24,600.00 | $26,291.95 | |||||||
| 112968 | Southeast | $904.20 | $1,722.00 | $0.00 | $0.00 | $2,626.20 | |||||||
| 113024 | Northeast | $3,616.61 | $2,439.50 | $1,865.84 | $6,923.26 | $14,845.21 | |||||||
| 113076 | Southwest | $1,938.09 | $5,795.86 | $15,612.14 | $0.00 | $23,346.09 | |||||||
| 113202 | Southwest | $21,938.58 | $474.80 | $474.76 | $861.21 | $23,749.35 | |||||||
| 113744 | Northeast | $556.12 | $4,528.15 | $2,459.92 | $7,593.46 | $15,137.64 | |||||||
| 113996 | Northeast | $3,969.58 | $0.00 | $0.00 | $11,275.00 | $15,244.58 | |||||||
| 113998 | Northeast | $334.28 | $513.32 | $14,054.80 | $0.00 | $14,902.40 | |||||||
| 114358 | Northwest | $822.00 | $328.00 | $492.00 | $984.00 | $2,626.00 | |||||||
| 114686 | Northwest | $1,227.99 | $579.64 | $579.58 | $388.44 | $2,775.66 | |||||||
| 114894 | Southwest | $815.15 | $0.00 | $0.00 | $23,165.00 | $23,980.15 | |||||||
| 114914 | Northeast | $8,225.48 | $6,285.30 | $0.00 | $0.00 | $14,510.78 | |||||||
| 114934 | Southwest | $1,559.03 | $0.00 | $2,506.72 | $21,785.11 | $25,850.86 | |||||||
| 114950 | Northeast | $11,229.34 | $0.00 | $3,955.53 | $0.00 | $15,184.87 | |||||||
| 115040 | Southeast | $2,751.11 | $0.00 | $300.00 | $0.00 | $3,051.11 | |||||||
| 115282 | Northeast | $1,715.38 | $6,029.46 | $5,451.36 | $1,290.68 | $14,486.88 | |||||||
| 115582 | Southwest | $956.70 | $21,684.70 | $2,086.70 | $1,840.70 | $26,568.80 | |||||||
| 115808 | Southwest | $1,041.34 | $0.00 | $7,912.17 | $17,086.64 | $26,040.14 | |||||||
| 116172 | Southwest | $6,455.44 | $8,323.00 | $9,430.00 | $410.00 | $24,618.44 | |||||||
| 116368 | Northwest | $528.81 | $1,640.00 | $820.00 | $0.00 | $2,988.81 | |||||||
| 116504 | Northwest | $1,242.77 | $579.60 | $579.67 | $579.69 | $2,981.73 | |||||||
| 116640 | Southwest | $3,390.50 | $18,629.15 | $0.00 | $0.00 | $22,019.64 | |||||||
| 116704 | Northeast | $2,145.65 | $2,716.66 | $300.00 | $9,926.10 | $15,088.41 | |||||||
| 116706 | Northwest | $1,970.07 | $0.00 | $0.00 | $723.87 | $2,693.94 | |||||||
| 116964 | Southwest | $11,579.48 | $0.00 | $11,890.74 | $0.00 | $23,470.22 | |||||||
| 117048 | Northwest | $2,603.00 | $0.00 | $300.00 | $0.00 | $2,903.00 | |||||||
| 117298 | Northeast | $7,251.96 | $2,280.42 | $1,517.82 | $4,174.64 | $15,224.84 | |||||||
| 117630 | Northeast | $3,107.28 | $0.00 | $0.00 | $11,799.80 | $14,907.08 | |||||||
| 117764 | Northwest | $1,472.04 | $0.00 | $0.00 | $1,159.31 | $2,631.34 | |||||||
| 117988 | Northeast | $12,494.40 | $0.00 | $2,460.00 | $0.00 | $14,954.40 | |||||||
| 118042 | Southeast | $341.54 | $468.51 | $300.00 | $1,608.30 | $2,718.35 | |||||||
| 118234 | Northeast | $1,716.16 | $1,976.20 | $1,211.14 | $10,143.40 | $15,046.90 | |||||||
| 118288 | Northeast | $593.15 | $692.90 | $4,048.34 | $9,229.92 | $14,564.31 | |||||||
| 118724 | Northeast | $4,988.39 | $0.00 | $5,740.00 | $4,373.06 | $15,101.45 | |||||||
| 118732 | Northeast | $3,004.55 | $300.00 | $909.94 | $10,524.12 | $14,738.60 | |||||||
| 118836 | Southwest | $6,790.71 | $15,188.86 | $0.00 | $2,050.00 | $24,029.57 | |||||||
| 119276 | Southwest | $2,603.00 | $0.00 | $21,403.64 | $0.00 | $24,006.64 | |||||||
| 119334 | Northwest | $779.01 | $723.87 | $723.86 | $723.89 | $2,950.64 | |||||||
| 119386 | Northwest | $761.72 | $787.20 | $787.20 | $664.20 | $3,000.32 | |||||||
| 119504 | Southwest | $7,699.93 | $2,003.12 | $11,459.50 | $5,714.85 | $26,877.40 | |||||||
| 119608 | Southeast | $516.55 | $0.00 | $1,158.93 | $1,159.22 | $2,834.70 | |||||||
| 120026 | Southwest | $19,619.95 | $0.00 | $0.00 | $5,084.00 | $24,703.95 | |||||||
| 120098 | Northwest | $383.60 | $1,312.00 | $492.00 | $492.00 | $2,679.60 | |||||||
| 120148 | Northwest | $739.80 | $300.00 | $300.00 | $1,476.00 | $2,815.80 | |||||||
| 120162 | Southwest | $6,254.60 | $1,305.79 | $7,243.69 | $8,271.48 | $23,075.56 | |||||||
| 120370 | Southeast | $423.86 | $862.47 | $815.04 | $862.44 | $2,963.81 | |||||||
| 120518 | Southwest | $11,799.18 | $0.00 | $0.00 | $14,901.40 | $26,700.58 | |||||||
| 120550 | Northwest | $1,368.40 | $0.00 | $0.00 | $1,437.42 | $2,805.82 | |||||||
| 120786 | Southeast | $2,699.08 | $508.67 | $0.00 | $0.00 | $3,207.75 | |||||||
| 120840 | Northeast | $6,582.59 | $0.00 | $0.00 | $7,913.10 | $14,495.69 | |||||||
| 120996 | Southeast | $825.74 | $688.80 | $688.80 | $541.20 | $2,744.54 | |||||||
| 121002 | Northwest | $548.00 | $300.00 | $1,763.00 | $300.00 | $2,911.00 | |||||||
| 121150 | Southeast | $2,125.62 | $0.00 | $0.00 | $809.34 | $2,934.96 | |||||||
| 121362 | Northeast | $5,607.83 | $3,034.00 | $3,091.48 | $3,681.80 | $15,415.11 | |||||||
| 121428 | Northwest | $1,609.64 | $452.12 | $452.07 | $452.12 | $2,965.93 | |||||||
| 121564 | Southwest | $21,335.17 | $1,025.00 | $615.00 | $615.00 | $23,590.17 | |||||||
| 121826 | Southwest | $7,401.01 | $0.00 | $14,775.59 | $0.00 | $22,176.60 | |||||||
| 121856 | Southwest | $4,153.82 | $13,309.57 | $6,251.56 | $300.00 | $24,014.94 | |||||||
| 121884 | Southwest | $13,417.05 | $11,628.22 | $300.00 | $0.00 | $25,345.27 | |||||||
| 121920 | Northeast | $1,038.08 | $0.00 | $2,662.03 | $11,298.78 | $14,998.89 | |||||||
| 122366 | Northeast | $3,381.04 | $11,327.48 | $0.00 | $0.00 | $14,708.52 | |||||||
| 122418 | Northwest | $1,471.65 | $1,353.00 | $0.00 | $0.00 | $2,824.65 | |||||||
| 122806 | Southwest | $23,296.20 | $0.00 | $300.00 | $300.00 | $23,896.20 | |||||||
| 122966 | Southwest | $22,220.52 | $1,558.00 | $300.00 | $1,271.00 | $25,349.52 | |||||||
| 123124 | Northeast | $1,146.88 | $13,460.30 | $0.00 | $0.00 | $14,607.18 | |||||||
| 123282 | Southwest | $2,188.70 | $4,732.70 | $11,040.92 | $8,361.35 | $26,323.68 | |||||||
| 123540 | Southeast | $360.00 | $0.00 | $300.00 | $2,378.00 | $3,038.00 | |||||||
| 123576 | Northwest | $414.70 | $618.28 | $927.35 | $927.42 | $2,887.76 | |||||||
| 123596 | Southeast | $305.40 | $888.27 | $902.11 | $880.39 | $2,976.17 | |||||||
| 123664 | Southwest | $3,774.35 | $0.00 | $0.00 | $22,575.58 | $26,349.93 | |||||||
| 123754 | Northeast | $2,328.80 | $7,212.72 | $3,275.54 | $2,456.72 | $15,273.78 | |||||||
| 123780 | Southeast | $669.46 | $913.13 | $696.55 | $696.81 | $2,975.95 | |||||||
| 123838 | Northwest | $2,501.89 | $0.00 | $0.00 | $300.00 | $2,801.89 | |||||||
| 123844 | Southeast | $2,342.70 | $300.00 | $300.00 | $300.00 | $3,242.70 | |||||||
| 123980 | Southeast | $411.00 | $1,042.06 | $615.00 | $1,025.00 | $3,093.06 | |||||||
| 124016 | Southeast | $2,219.06 | $300.00 | $300.00 | $300.00 | $3,119.06 | |||||||
| 124118 | Northeast | $4,488.87 | $4,888.02 | $4,691.22 | $1,353.82 | $15,421.93 | |||||||
| 124202 | Northwest | $494.51 | $1,122.67 | $750.86 | $631.10 | $2,999.13 | |||||||
| 124320 | Southwest | $1,058.13 | $2,618.19 | $13,656.69 | $4,975.64 | $22,308.65 | |||||||
| 124750 | Southwest | $22,281.68 | $300.00 | $0.00 | $0.00 | $22,581.68 | |||||||
| 124946 | Southwest | $4,493.16 | $0.00 | $18,483.62 | $1,722.00 | $24,698.78 | |||||||
Tables
| Use if statements to answer these questions | |
| 1. If the total purchases if greater than 25000 then the region bonus is 1 percent of the total purchases otherwise it is 0. Place the answer in the Region Bonus column. | |
| 2. Place the result of the following formula in the Validate column, If the total purchases isgreater or equal to 3000 and less than or equal to 27000 then leave the cell blank otherwise place the word verify in the column. | |
| 3. Use a nested if statement to calculate the Travel Adjustment according to the table below and place the answer in the Travel Adjustment Column, | |
| Region | Travel Adjustment |
| Southeast | 500 |
| Southwest | 700 |
| Northeast or Northwest | 100 |
| 4. If the total purchases in week 3 plus week 4 is greater or equal to the total purchases in week 1 plus week 2 then place the word Higher in the Improvement Column otherwaise place the word Lower in the column. | |
| 5. If the Region is Northeast or Northwest and the total purchases is greater than 20000 then the North bonus is 500 dollars. | |
| 6. If the Region is Southeast or Southwest and the total purchases is greater than 18000 then the South bonus is 700 dollars. | |
| 7. If the Region is Northeast or Southeast and the total purchases are less than 2500 then place the words Odd Result in the Result column. |
