Practice MidtermFunctionsAND(logical1, [logical2],…)AVERAGE(number1, [number2]…)AVERAGEIF(range, criteria, [average_range])COUNT(value1, [value2],…)COUNTIF(range, criteria)RANK.EQ(Number, Ref, [Order])ROUND(number,num_digits)SMALL(array, k)LARGE(array, k)SUM(number1, [number2],…)SUMIF(range, criteria, [sum_range])AVERAGEIF(range, criteria, [average_range])MAX(number1, [number2],…)MIN(number1, [number2],…)NOT(logical)OR(logical1, [logical2],…)FV(rate, nper, pmt, [pv], [type])NPER(rate, pmt, pv, [fv], [type])PMT(rate, nper, pv, [fv], [type])PV(rate, nper, pmt, [fv], [type])RATE(nper, pmt, pv, [fv], [type])IF(logical_test,[value_if_true],[value_if_false])HLOOKUP(lookup_value, table_array, row_index_no,[ range_lookup_type])VLOOKUP(lookup_value, table_array, col_index_no, [range_lookup_type])SUMIFS(sum_range,criteria_range1,criteria1,…)AVERAGEIFS(average_range,criteria_range1,criteria1,…)COUNTIFS(criteria_range1,criteria,…)1

Practice MidtermProblem:Congratulations, you’ve just accepted a new job in Florida; the only hitch is you’ve got to sell your lovely 3 bedroom home here in Columbus. The realtors who have been through have commented onthe cleanliness and urged you to hire professional cleaning services to clean your floors, heating ductsystem, windows and lighting fixtures. Before embarking on this plan you’d like to get an estimate of the costs that are involved, hence you have developed an Excel workbook and input the quotes you have received from local cleaning service specialties.You have created an Excel workbook with five spreadsheets as listed below and now will be writing formulas to calculate by room the costs involved. Sheet dimcontains information regarding the purpose of each room (public or private), the typeof floor (carpet, wood, or tile), the number of lighting fixtures, and the number of windows, in that room. Dimensions for the room height are given in feet. Dimensions for room length and width are given the number of feet and inches. You will need to convert these dimensions to feet (ex: 12 foot 3 inches equals 12.25 feet) when calculating area. You will also be estimating the number of heating registers (vents) for each room based on the volume of the room (length * width * height).