Excel Functions for Administrative Calculations
Translated by
ScribdTranslationsExcel Functions for Administrative Calculations
Translated by
ScribdTranslationsInteramerican University of the North
EXERCISE PRACTICE
Nombre: YENILIANA RUIZ LUCIDO Matrícula: 181042447
Subject name: Subject Tutor:
Advanced Computing Joel Vega
Actividad:
Spreadsheet (Week 3 Activity)
Date: Team:
Sources consulted:
Excel 2016 Support [Link]
Objective
Identify the Excel functions for administrative calculations.
Procedure
I.- Using Excel formulas, fill in the empty cells:
Subtotal
We calculate the subtotal by multiplying the quantity by the unit price and we do it in the
the following way in Excel.
We place ourselves in the subtotal cell of the first value and enter this formula: =C3*D3
and it will perform the multiplication to create the subtotal.
Now, to put the subtotal for each of the products we do the following
steps:
We again position ourselves in the subtotal cell of the first product and
we press CTRL+C to copy the formula and then select the cells in which
we want to copy the formula and we press CTRL+V to paste the formula.
VAT
To obtain the VAT, the subtotal must be multiplied by 16%, that is, it must be multiplied.
for 0.16. So in the table it would look like this.
To calculate VAT in all the cells we want, we perform the same procedure.
que en el paso anterior, copiamos la formula con CTRL+C y luego seleccionamos las celdas
where we want to paste the formula and press CTRL+V and that's it.
Amount
To calculate the amount, the Subtotal + VAT is added as shown in the following
image.
We will also copy the formula and paste it into the desired cells to apply the
import.
II.- Answer the questions considering the figure, only use Excel formulas
1) Describe the steps to center the column headers.
R=Select the column headers and go to the home bar and in the
Alignment section, select center. Or also using the combination of
ALT+O+E keys.
2) Describe the steps to add borders to the spreadsheet.
R=Select the area you want to border and click on the Home tab and
in the Source section select this tool and then select the
options you want.
3) Describa los pasos para dar formato a la columna importe para poner 2 decimales y
thousands separator.
R=Select the first cell of Amount and go to the Home tab in the bar.
tools and in the Number section click on these buttons to increase
decimals and to make the separation in thousands.
4) Mention the difference between absolute reference and relative reference.
RTherelative referenceIt refers to a cell whose formula can change to adjust.
to your new position. The relative reference is identified by the cell title,
interception letter of column and row number. Example D5, G6, H7
Theabsolute referencerepresents the value of a cell that will not change if moved to
another cell. The absolute reference is identified by placing a dollar sign in front of the
columna y fila de la celda que se desea trabajar. Ejemplo $H$9, $M$36, $P$20
5) Write the formula to calculate the amount that results from dividing the requested credit
plus the surcharge percentage defined in the corresponding cell, divided by the number of
quotas established for each case.
R= (Credit +(credit*H4))/D4
D5
D6…
6) Write the formula to calculate the VAT, which is obtained by multiplying the amount.
fee for the percentage indicated in the corresponding cell.
RE4*0.23.
7) Write the formula to calculate the total, which is the sum of the amount plus the VAT.
R==B4*H7+B4.
8) Write the formula to sum the total fee.
R=SUM(F4:F8)
9) The following is a valid reference: 5A:B4 _TRUE
10) Write an absolute cell reference: ___$H$7__
Bibliography
TARRACHANO, I. M.–L. (s.f.).Manual de Excel 2016.