Please finish each question. ..................ddddddd

Part.Page # 1 Lookup: 1.02 1 1 1 Write a formula that will show the related price when the item number is entered. Item Description Price 1000 Pen 2000 Clip 3000 Tie 1 1 1 10 20 30 1000 1 1 2 Format: 2.02 2 2 Write a formula that will show one more than the value of the if the number is greater than 100, or show one less if it is not. 2 50 2 2 3 Format: 2.02 3 3 Format cell to show the date in the format of Monday, October 17, 2011. 3 10/17/2011 3 3 4 IF: 2.03 4 4 4 Write a formula that would indicate if the sum of the first column of numbers did not equal the sum of the second column by displaying either Equal or Not Equal. 4 2000 1000 4 4 500 2500 4 4 4 5 Format: 1.01 5 Format cells so that the heading Debit is on a 45 degree angle. 5 5 Debit 5 3.05 6 Validation: 6 Validate so that the first credit cell of a journal entry cannot have a value in it. 6 6 6 6 6 6 7 IF: Debit Credit 2.09 7 7 Write a formula that would indicate if the value in a cell has more than two decimal places by displaying "Round to 2 Decimal Places". 7 Debit 7 Credit 100 753 7 7 500.004 353 7 7 2.06 8 IF: 8 Write a formula that will indicate if there are credits before debits 8 Debit 8 Credit 1000 8 2000 8 1000 8 8 9 Format: WS.03 9 9 Change the format to center trial Balance over the two cells. Trial Balance 9 2.03 10 Freeze Panes: 10 10 Freeze column A of the spreadsheet to make sure the title does not scroll off the left side of the screen. 10 10 IF: TB.5 11 Write the formula to indicate if the trial balance was not in balance 11 11 Trial Balance Debit Credit 25,000 12,000 11 11 11 11 11 11 14,000 11 11 11 25,000 11 26,000 11 11 11 12 data validation 3.02 12 Create an error alert message for dates that are not in the month of March 2020. 12 12 12 13 Calculate the profit or the loss. 13 13 13 13 13 Computer Revenue Interest Expense Telephone Expense Income Statement Debit Credit 1,500 300 200 13 13 13 13 Total Net Income 500 1,500 13 14 14 Protect all non highlighted cells and unprotect the highlighted cells 14 14 14 14 Date Trans 1-Jun-09 1-Jun-09 Account 65 3,100 65 1,110 14 14 14 15 15 15 15 15 15 Write a formula to lookup the animal corresponding to each number 1 Dog 2 Cat 3 Bird 4 Rabbit 3 15 15 16 16 16 Write a formula that will return "Great" if the value is greater than 0 and nothing if it is less than 0 2 16 16 17 Restrict the entry into a cell to a number less than 10. 17 17 3.03 18 Data Validation 18 The only valid entry is an account number from the chart of accounts 18 chart of accounts 1110 cash 1130 prepaid 1211 office equip 18 18 18 18 18 19 Data Validation: 19 19 Enter only positive numbers, with an error message if it is negative. 19 19 19 2.07 20 IF: If the line is missing an account number display "Need an Account Number" 20 20 Account 20 Name Debit 1110 Cash 1211 Marketable Sec 20 20 Credit 500 400 100 20 3100 Capital Stock 20 1000 20 21 5/1/09 5/1/09 21 21 un t co 21 Create a formula that will increment the transaction number if there is an account number Ac 21 s 21 ` Tr an 21 Da te 1.04 1 1 1110 5100 21 5/1/09 5/1/09 21 21 1110 1311 21 21 21 22 Custom format cell to display "Feb 17" 22 2/17/2009 22 22 22 3.02 23 Format: 23 23 Format cells so that a stop pops up when they enter something in besides a date in the cell. 23 23 23 23 3.02 24 Format: 24 24 24 24 24 24 24 Format cells so that an error alert message tells the user "Please enter the date of the transaction" when they type in something other than the date in the highlighted cell. 2/17 Remove decimal places from account numbers. 24 Number 1110.000000 1130.000000 1211.000000 1311.000000 2101.000000 24 3.02 25 25 25 25 25 25 25 3.02 Name the range "code" 26 Number 26 1110 1130 1211 1311 2101 26 26 26 26 26 Name Hat Coat Shoe Ball Pen 26 26 5.07 27 IF: Write a formula that would display the color in the Bright column if its rank is three or less and in the Dark column if higher. 27 27 27 Color Green Orange Yellow Black Brown Grey 27 27 27 27 27 27 27 Bright Rank Dark 1 2 3 4 5 6 27 27 27 28 28 IF: Write a formula that would separate the gains from the loses. 28 28 28 28 28 28 28 28 28 28 28 28 28 Player Maria John Andrew Stephanie David Ashley Gain/Loss 120000 -450000 675000 48500 -9875 -4500 CASINO ROYALE Gains Losses 28 29 29 29 29 29 29 IF: Rent-a-Boat has different locations in the East and West Coast. Locations are assigned a code based on their State. States in the West Coast have numbers between 1 and 3, and states in the East Coast have codes between 4 and 6. Write a formula that will display only cities in the East Coast and that will display Net Income to the left and Net Loss to the right. 29 East Coast 29 29 29 29 29 29 29 29 29 29 29 29 29 City San Diego New York City Miami Buffalo Los Angeles Jacksonville Salem Atlanta Seattle Astoria Ft Lauderdale Code 1 6 4 6 1 4 2 5 3 2 4 Net Income/Loss Net Income -320000 375000 296500 -179800 452000 298400 -120000 330000 477800 -265000 -180000 Net Loss 29 30 2.08 30 30 The dollar amounts have three decimal places. Write a formula that will round down to two decimal places. 30 123.249 30 30 30 2.08 31 31 The dollar amounts have three decimal places. Write a formula that will round up to two decimal places. 31 123.249 31 31 2.08 32 32 The dollar amounts have three decimal places. Write a formula that will round to two decimal places. 32 123.249 32 32 32 32 32 33 Display an error if there is a value in the Current and Late cells for the same invoice. 33 33 33 Invoice Amount Current Late 33 33 AM1254 AM1255 120.00 200.00 100.00 20.00 200.00 33 33 33 33 1.03 34 Lookup: 34 34 34 Write a formula that will finds the code for the color or return #N/A if the color is not on the list. Color Code Red Blue Yellow Orange Green Black White 34 34 34 34 34 34 34 101 102 103 104 105 106 107 Green 34 34 35 Lookup: 35 35 Write a formula that will show the score when the team is entered. 35 Team 35 Heat Nuggets Bulls Magic Lakers Celtics 35 35 35 35 35 35 City Score Miami Denver Chicago Orlando L.A Boston 96 80 103 89 94 112 35 Magic 35 35 35 TB.03 36 Heading Formatting: 36 36 36 Centered across the selection.. Accounting 36 37 W.10 38 37 37 37 37 37 37 Show the latest date 2/5/2009 1/15/2009 10/12/2009 8/30/2009 WS.9 39 39 Show the net income amount in the debit column if it's positive, and in the credit if it's negative 39 Debit 39 Total 39 Income Statement Credit 400 600 39 Net Income 39 39 39 40 40 Change the cell to only allow 5 characters. 40 40 41 WS.03 41 Center Across Columns and wrap the words in the cell. 41 41 My best class is accounting because I learn so much. 41 41 3.03 41 41 Format Allow only an account number from the list in the cell. 41 41 chart of accounts 1110 cash 1130 prepaid 1211 office equip 41 41 41 41 41 42 TB.01 42 Write a formula that sums the assets 42 42 42 1110 1130 5100 5120 1140 42 42 42 42 42 Cash Prepaid Insurance Telephone Rent Accounts Receivable Amount 1500 1000 200 400 600 42 42 42 42 43 43 Write a formula that will create an error message if the transaction is not in balance 43 43 43 Name Cash Description Joe's Debit 8000 Credit 43 8050 Capital Stock Joe's 43 43 44 1.4 44 44 44 44 Change the format of the dates to display them as MM DD, YYYY 06/28/88 03/30/99 06/21/00 45 1.4 45 45 45 Have the date repeat in the below cell when an account number is entered Date Account 06/01/09 1000 45 45 45 Formatting: 3.01 46 Put a green border around the cells. 46 46 46 46 46 46 46 47 Validation: 3.01 47 Reject any input that is not a date between 1/1/1950 and 12/31/1951 47 47 47 45 2.02 48 48 48 48 48 Write a formula that would indicate that there is a difference between the debits and credits and the amount of the difference. Debits Credits 2500 5000 6000 500 48 48 48 48 49 2.02 49 Format the cell to show the amount owed after the words "You owe" 49 49 49 49 49 49 50 you owe 100 500 600 50 Name the columns and calculate the sum using the names. 50 January 10 20 35 20 50 50 50 50 50 February 50 30 50 70 Total 50 51 3.03 51 Data Validation: 51 Restrict the pay to a positive number. 51 Name Jim Kat Karen 51 51 51 51 51 51 52 FS.01 52 IF, Lookup: 52 Indicate if the balance is different than the normal balance. 52 52 52 52 52 52 1110 1130 3100 2200 Cash Prepaid Ins Capital Stock Dividends Debit Debit Credit Debit Account 1110 3100 Balance 100.25 200 52 52 52 52 52 53 2.09 54 54 54 Write a formula that will indicate if is a cell is not rounded to 2 decimal places by displaying "Round to 2 decimal Places" 54 54 54 1011.5657 54 55 55 55 55 55 55 Format the cell to be yellow if the value exceeds 100 Pay 20 50 55 55 Enter a formula that automatically increases the Week Ending by 7 when a new sales amount is entered. 55 55 55 55 55 Week Ending 1/1/2009 1/8/2009 Sales Amount 1000 2000 55 55 55 2.07 56 56 Write a formula that will display a "Not Equal" when the values in the two cells are not equal (and display nothing when they are equal). 56 56 56 Total Debits 1,205.00 Total Credits 1,205.00 56 56 56 57 IF: 2.07 57 57 57 Write a formula that will indicate if an account number is missing by displaying Need an Account Number if there is a value under Balance without a value under Account Number. 57 57 Account # Balance 545.00 57 57 57 57 Lookup: 1.05 58 58 58 Write a formula that will show the corresponding tax rate for the amount of income. Income Tax Rate 15,000.00 35,000.00 60,000.00 58 58 58 0.15 0.25 0.33 60,000.00 58 58 59 WS.02 59 59 Change the numeric format to ##,###.00 cans 50000 59 125.3 59 59 60 FS 60 60 Lookup: 60 60 Write a formula that would show the related description when the account number is entered 125.3 60 Account # Account Desc. 1,110.00 Cash 1,130.00Prepaid Insurance 1,211.00 Office Equip. 60 60 60 60 1,130.00 60 60 61 61 Lookup: 61 61 Write a formula that would show the related amount when the account number is entered 61 61 61 61 61 Account # Account Desc. Amount 1,110.00 Cash 140,000.00 1,130.00Prepaid Insurance 4,600.00 1,211.00 Office Equip. 38,000.00 1,110.00 61 61 61 62 Rounding: 62 62 Round to two decimal places. 12,345.23 62 63 Rounding: 63 In cell H36 round D36 to the next higher integer. 63 63 12,345.23 63 63 64 IF: 64 Write a formula that would display the difference between the two columns 64 64 64 64 D E 2,000.00 1,200.00 500.00 2,500.00 64 64 64 64 64 65 IF: 65 65 65 Write a formula that will indicate that ALL the values are " All Greater than 10" or that they are "Not all greater than 10". 12.00 14.00 45.00 65 65 66 Date Comparison: 66 66 66 66 Write a formula that will show the LATEST date in the range D113:D115. 1/2/2007 4/5/2006 7/30/2016 66 66 67 IF: 67 67 67 Write a formula that will indicate that at least one of the values are greater than 10 by displaying At Least One is Greater Than 10 or that by displaying None are Greater than 10. 1.00 1.00 14.00 67 67 68 IF: 68 Increment the line number by one if a new name is added 68 68 Name Mike 68 68 Line Number 88.00 68 68 68 68 68 70 70 IF: 70 70 70 70 70 70 70 70 70 Displays "Markdown" if the product is a Marker, or if the product is a Red Pen that costs more than $6. Displays "Regular" if not. Product Color Price Pen Red 2.00 Marker Black 6.00 Pen Red 7.00 Marker Black 4.00 Pen Red 5.00 Marker Black 3.00 70 71 71 Sumif: 71 71 71 Indicate the sum of the prices of the product selected . Marker 71 72 72 72 72 73 Conditional Formatting: Make the fill color Light Blue if the value is greater than 100. 124.00 73 Data Validation: 73 Reject any negative number. 73 73 74 74 Data Validation: 74 Reject any date that is not in 2008. 74 74 75 75 IF: Write a formula in H61 that will indicate that exactly two of the values in C60,D60 75 and E60 are greater than 10 by displaying Two are Greater than 10 75 or that by displaying Two are Not Greater than 10. 12.00 2.00 14.00 75 75 76 Name; 75 Name Hours and Rate and write a formula using the named ranges 76 Name Tom Mary Peter 76 76 76 76 Hours 10.00 20.00 40.00 Rate 2.00 3.00 4.00 76 76 76 77 IF: 77 77 Follow the rules in the following table and write "Discount" or "No Discount" in cell H133 based upon cells D133 and E133. 77 RED and >10 Discount 77 GREEN and >15 Discount 77 Blue and > 20 Discount 77 77 RED 12.00 77 77 78 IF: 78 Indicate if they are all "A's" 78 78 78 A A C