Ac 389 Screen Shots of in-Class Demo Project 1
Accounting Information Systems
Excel 1, SUMIF, IF, VLOOKUP
To access the functions, click on the Insert Function (fx) icon. You can select the most recently used, all functions or functions by category.
Find SUMIF.
SUMIF function
In the Range field, click and drag the range of cells that contain the data used in the Criteria field. In this case, the number field contains the data used in the Criteria field (number).
In the Criteria field define the criteria (in this case, number = 1).
In the Sum_range field click and drag the range of cells that contain the data that you want to sum. In this case, the amount field (column B) is being summed.
IF function
In the Logical_test field, define the criteria. In this case the data in column A = 1.
In the Value_if_true field, describe the output for the function if the data in column A matches the criteria. In this case, if the data in column A = 1, the word “one” is entered in the cell. Note that quotations are used to signify that the output is text.
In the Value_if_false field, describe the output for the function if the data in column A does not match the criteria. In this case, if the data in column A ≠ 1, the word “two” is entered in the cell.
After completing the formula in cell D3, copy the formula to cells D4 and D5.
VLOOKUP function In this case, I want to assign a name to the amount data. To do this, the numbers in A3 – A-5 (amount data, which doesn’t contain name) is matched to the numbers in A11 – A12 (name data, which does contain name).
In the Lookup_value field, enter the cell in the amount data that will be used to match to the name data. In this case, A3.
In the Table_array field, enter the name data (i.e., A11:B12). The field that you are matching on (number) has to be the first column in the Table_array. Note that I added dollar signs to the Table_array field. When dollar signs are placed in front of a column or row, that column or
Excel 1, SUMIF, IF, VLOOKUP
To access the functions, click on the Insert Function (fx) icon. You can select the most recently used, all functions or functions by category.
Find SUMIF.
SUMIF function
In the Range field, click and drag the range of cells that contain the data used in the Criteria field. In this case, the number field contains the data used in the Criteria field (number).
In the Criteria field define the criteria (in this case, number = 1).
In the Sum_range field click and drag the range of cells that contain the data that you want to sum. In this case, the amount field (column B) is being summed.
IF function
In the Logical_test field, define the criteria. In this case the data in column A = 1.
In the Value_if_true field, describe the output for the function if the data in column A matches the criteria. In this case, if the data in column A = 1, the word “one” is entered in the cell. Note that quotations are used to signify that the output is text.
In the Value_if_false field, describe the output for the function if the data in column A does not match the criteria. In this case, if the data in column A ≠ 1, the word “two” is entered in the cell.
After completing the formula in cell D3, copy the formula to cells D4 and D5.
VLOOKUP function In this case, I want to assign a name to the amount data. To do this, the numbers in A3 – A-5 (amount data, which doesn’t contain name) is matched to the numbers in A11 – A12 (name data, which does contain name).
In the Lookup_value field, enter the cell in the amount data that will be used to match to the name data. In this case, A3.
In the Table_array field, enter the name data (i.e., A11:B12). The field that you are matching on (number) has to be the first column in the Table_array. Note that I added dollar signs to the Table_array field. When dollar signs are placed in front of a column or row, that column or