# Excel Mod. 3 Training

question
Enter a formula in cell D5 that divides the value in cell C5 by the value in cell C17, using an absolute cell reference to cell C17.
1.) Select cell D5 2.) type (=) 3.) click cell C5 4.) type (/) after the C5 cell reference. 5.) click cell C17 6.) press the (f4) key. 7.) press enter.
question
Enter a formula using arithmetic operators and parentheses in cell B14 that adds the monthly expenses in cells B9, B10, and B11, and then multiplies that result by 12.
1.) select cell B14 2.) type "=(". 3.) select cell B9. 4.) type (+) in cell B14 5.) click cell B10 6.) type (+) 7.) click cell B11 8.) type ")" in cell B14. 9.) type (*12) in cell B14. 10.) click enter on the formula bar.
question
In cell F4, enter a formula using the IF function that returns a value of YES if cell E4 is greater than 0 (zero), and a value of NO if not.
1.) select cell F4. 2.) on the formula bar insert the function button. (fx) 3.) click the "Or select a category arrow" 4.) click logical 5.) click IF 6.) click ok. 7.) type E4>0. In the logical test box. 8.) press tab 9.) type YES in the "value_if_true box." 10.) Press tab. 11.) type NO in the "Value_if_False box." 12.) click ok.
question
In cell E16, create a formula using the MAX function to calculate the maximum value in the range E4:E14.
1.) click cell E16. 2.) insert (fx) buttion. 3.) click the Or select a category arrow. 4.) click statistical 5.) click MAX 6.) click ok. 7.) type E4:E14 in the Number1 box. 8.) click ok.
question
In cell E17, create a formula using the MIN function to calculate the minimum value in the range E4:E14.
1.) select cell E17. 2.) In the Editing group, click the AutoSum arrow. 3.) click 'min' in Autosum 4.) select cells E4:E14 5.) press enter.
question
In cell B12, use the Quick Analysis tool to create a formula that uses the SUM function to total the values in the range B3:B11.
1.) select B3:B11 2.) click the quick analysis button. 3.) click Totals. 4.) click the Sum option.
question
In cell B2, create a formula using the TODAY function to display the current date.
1.) select cell B2 2.) click Formulas tab on the ribbon 3.) in the Function Library group, click the Date & Time button. 4.) click Today 5.) click ok.
question
In cell E5, create a formula using the VLOOKUP function to determine the bonus percentage for employee Jessica Chang based on the performance rating in cell C5. Use range H4:I8 as the lookup table, and the bonus percentage listed in column 2 of the lookup table. Do not enter a value for the optional range_lookup argument.
1.) select cell E5 2.) type =VLOOKUP(C5,H4:I8,2) in cell E5. 3.) press enter.
question
In cell B7, enter a formula using relative cell references that subtracts cell B6 from cell B5.
1.) click cell B7 2.) type =B5-B6 in cell B7.
question
Use the fill handle to copy the formula in cell B7 to cells C7 and D7.
1.) click cell B7 2.) drag cell B7 to cell D7
question
Use Goal Seek to calculate the changing value in cell B5 that will result in a set value in cell B7 of 0.3, or 30%.
1.) click cell B7 2.) click the Data tab on the Ribbon. 3.) click "What-If Analysis" in Forecast group. 4.) Click Goal Seek. 5.) click To Value text box. 6.) type 0.3 and press tab 7.) click cell B5 8.) click ok.
question
Use the AutoFill feature to fill the range A4:A15 with the names of the months, in chronological order, starting with January in cell A4.
1.) click cell A4 2.) type January 3.) click and drag to cell A15.
question
In cell B6, create a formula using the ROUND function that rounds the value in cell J6 to an integer, with 0 (zero) decimal places.
1.) click cell B6 2.) type =Round( in cell B6. 3.) click cell J6 4.) typ ,0) in cell B6. 5.) click the enter button on the formula bar.
question
In cell D21, enter a formula using the MEDIAN function to calculate the median value in range D4:D18.
1.) click cell D21 2.) click (fx) on formula bar 3.) type Median in the search for a function text box. 4.) click the Go button. 5.) click ok. 6.) type D4:D18 in the Number1 text box. 7.) click Ok.
question
Constants
what are terms in a formula that don't change their value?
1 of 15

## Unlock all answers in this set

question
Enter a formula in cell D5 that divides the value in cell C5 by the value in cell C17, using an absolute cell reference to cell C17.
1.) Select cell D5 2.) type (=) 3.) click cell C5 4.) type (/) after the C5 cell reference. 5.) click cell C17 6.) press the (f4) key. 7.) press enter.
question
Enter a formula using arithmetic operators and parentheses in cell B14 that adds the monthly expenses in cells B9, B10, and B11, and then multiplies that result by 12.
1.) select cell B14 2.) type "=(". 3.) select cell B9. 4.) type (+) in cell B14 5.) click cell B10 6.) type (+) 7.) click cell B11 8.) type ")" in cell B14. 9.) type (*12) in cell B14. 10.) click enter on the formula bar.
question
In cell F4, enter a formula using the IF function that returns a value of YES if cell E4 is greater than 0 (zero), and a value of NO if not.
1.) select cell F4. 2.) on the formula bar insert the function button. (fx) 3.) click the "Or select a category arrow" 4.) click logical 5.) click IF 6.) click ok. 7.) type E4>0. In the logical test box. 8.) press tab 9.) type YES in the "value_if_true box." 10.) Press tab. 11.) type NO in the "Value_if_False box." 12.) click ok.
question
In cell E16, create a formula using the MAX function to calculate the maximum value in the range E4:E14.
1.) click cell E16. 2.) insert (fx) buttion. 3.) click the Or select a category arrow. 4.) click statistical 5.) click MAX 6.) click ok. 7.) type E4:E14 in the Number1 box. 8.) click ok.
question
In cell E17, create a formula using the MIN function to calculate the minimum value in the range E4:E14.
1.) select cell E17. 2.) In the Editing group, click the AutoSum arrow. 3.) click 'min' in Autosum 4.) select cells E4:E14 5.) press enter.
question
In cell B12, use the Quick Analysis tool to create a formula that uses the SUM function to total the values in the range B3:B11.
1.) select B3:B11 2.) click the quick analysis button. 3.) click Totals. 4.) click the Sum option.
question
In cell B2, create a formula using the TODAY function to display the current date.
1.) select cell B2 2.) click Formulas tab on the ribbon 3.) in the Function Library group, click the Date & Time button. 4.) click Today 5.) click ok.
question
In cell E5, create a formula using the VLOOKUP function to determine the bonus percentage for employee Jessica Chang based on the performance rating in cell C5. Use range H4:I8 as the lookup table, and the bonus percentage listed in column 2 of the lookup table. Do not enter a value for the optional range_lookup argument.
1.) select cell E5 2.) type =VLOOKUP(C5,H4:I8,2) in cell E5. 3.) press enter.
question
In cell B7, enter a formula using relative cell references that subtracts cell B6 from cell B5.
1.) click cell B7 2.) type =B5-B6 in cell B7.
question
Use the fill handle to copy the formula in cell B7 to cells C7 and D7.
1.) click cell B7 2.) drag cell B7 to cell D7
question
Use Goal Seek to calculate the changing value in cell B5 that will result in a set value in cell B7 of 0.3, or 30%.
1.) click cell B7 2.) click the Data tab on the Ribbon. 3.) click "What-If Analysis" in Forecast group. 4.) Click Goal Seek. 5.) click To Value text box. 6.) type 0.3 and press tab 7.) click cell B5 8.) click ok.
question
Use the AutoFill feature to fill the range A4:A15 with the names of the months, in chronological order, starting with January in cell A4.