Ayuda Excel » EJEMPLOS Y APLICACIONES FÓRMULAS EXCEL
Ver
01

EJEMPLOS Y APLICACIONES FÓRMULAS EXCEL

A continuación se encuentran algunos ejemplos de cómo llevar una expresión matemática a su correspondiente fórmula en Excel.

a. Escribir la fórmula para la siguiente expresión matemática e indique el orden cómo se debe evaluar dicha fórmula para calcular el valor correspondiente. La fórmula en Excel se debe ingresar en la celda D3, con el valor de A en la celda D1, el valor de B en la celda E1 y el valor de C en la celda F1.

Como primer paso se debe escribir está expresión matemática como una fórmula que se pueda utilizar en la mayoría de los lenguajes de programación o en la mayoría de los programas de aplicación como es el caso del Excel. Siendo La primera dificultad escribir la fórmula en una sola línea, donde no se refleja el orden en que estamos acostumbrados a realizar las operaciones, quedando como:

A*B/RAIZ(C)/C-1/B-2*C/A

Si en esta fórmula analizamos el orden de evaluación que se tiene establecido para cada uno de los operadores[4], aplicando para cada operación el orden que se muestra en la tabla de precedencia de los operadores mostrada en la página 4, tenemos lo siguiente:

A*B/RAIZ(C)/C-1/B-2*C/A

2 3 1 4 5 . . . . . . . . .

Vemos que el resultado que se obtendrá al calcular la fórmula estará errado, ya que la división entre C, operación 4, se estaría realizando en un orden que va a alterar el resultado; por lo que se deben insertar paréntesis para cambiar este orden, quedando como:

A*B/RAIZ(C)/(C-1/B)-2*C/A

4 5 3 6 2 1 9 7 8

Esta fórmula, tal cual está escrita, sí calcula correctamente el valor de la expresión matemática, ya que las operaciones se hacen en el orden requerido o necesario. Una expresión matemática para ser utilizada en el computador puede ser escrita de varias formas equivalentes, ya que producen el mismo resultado final cambiando solamente el orden en que se realizan las operaciones. En este ejemplo podemos entonces tener las siguientes formas de escritura de la fórmula, que calculan correctamente el valor correspondiente a la expresión matemática indicada.

Forma 1: A*B/RAIZ(C)/(C-1/B)-2*C/A (forma básica)

4 5 3 6 2 1 9 7 8

Forma 2: (A*B/RAIZ(C))/(C-1/B)-2*C/A

2 3 1 6 5 4 9 7 8

Forma 3: (A*B/RAIZ(C))/(C-1/B)-(2*C/A)

2 3 1 8 5 4 9 6 7

La fórmula en Excel correspondiente debe ser ingresada en la celda D3, con el valor de A en la celda D1, el valor de B en la celda E1 y el valor de C en la celda F1.

Forma 1: En D3: =D1*E1/RAIZ(F1)/(F1-1/E1)-2*F1/D1

Forma 2: =(D1*E1/RAIZ(F1))/(F1-1/E1)-2*F1/D1

Forma 3: =(D1*E1/RAIZ(F1))/(F1-1/E1)-(2*F1/D1)

Sí ingresamos estás formulas en Excel, utilizando 1 como valor de A, 3 como valor de B y 4 como valor de C, obtenemos para las tres formas de escribir la fórmula el valor -7,59090909

Para observar que la única diferencia que hay entre estas 3 formas de escribir correctamente la fórmula en Excel de la expresión matemática es el orden en que se realizan los cálculos, se deben ingresar en una hoja de Excel las tres fórmulas, en diferentes celdas, donde se observa que el resultado es el mismo; y para observar cómo realiza los cálculos el Excel, se utiliza la opción Evaluar Formula, siguiendo el siguiente procedimiento:

- Seleccionar la celda donde está la fórmula

- Luego abrir el menú Herramientas

- Seleccionar la opción Auditoría de fórmulas

- Del nuevo menú que se presenta seleccionar Evaluar fórmula

- Para que se vaya realizando cada operación paso a paso presionar el botón Evaluar

- Para finalizar presionar el botón Cerrar

b. Escribir la fórmula para la siguiente expresión matemática e indique el orden cómo se debe evaluar dicha fórmula para calcular el valor correspondiente. La fórmula en Excel se debe ingresar en la celda E5, y que la celda E1 contiene el valor de A, la celda E2 el valor de B, la celda E3 el valor de C.

Forma 1: (2*A-B/RAIZ(C))/(C+1/B)*B/4 (forma básica)

2 4 3 1 7 6 5 8 9

Forma 2: (2*A-B/RAIZ(C))/(C+1/B)*(B/4)

2 4 3 1 8 6 5 9 7

La fórmula en Excel correspondiente debe ser ingresada en la celda E5, con el valor de A en la celda E1, el valor de B en la celda E2 y el valor de C en la celda E3.

Forma 1: En E5: =(2*E1-E2/RAIZ(E3))/(E3+1/E2)*E2/4

Forma 2: =(2*E1-E2/RAIZ(E3))/(E3+1/E2)*(E2/4)

Sí ingresamos estás formulas en una hoja de Excel, utilizando 2 como valor de A, 5 como valor de B y 9 como valor de C, obtenemos para las dos formas de escribir la fórmula el valor 0,31702899

c. La expresión matemática para el cálculo del monto de la cuota postpagable de un préstamo (la cuota se paga al final del período), viene dada como:

Escribir la fórmula correspondiente a esta expresión matemática e indicar el orden cómo se debe evaluar dicha fórmula para calcular el valor de la cuota postpagable:

Forma 1: VA*T*(1+T)^N/((1+T)^N-1) (forma básica)

6 7 1 5 8 2 3 4

Forma 2: VA*(T*(1+T)^N)/((1+T)^N-1)

7 3 1 2 8 4 5 6

Forma 3: VA*(T*(1+T)^N/((1+T)^N-1))

8 6 1 5 7 2 3 4

Forma 4: VA*((T*(1+T)^N)/((1+T)^N-1))

8 3 1 2 7 4 5 6

Si la celda B5 contiene el valor de la variable VA (valor actual), la celda B6 el valor de la variable T (tasa de interés), la celda B7 el valor de la variable N (número períodos), la celda B8 el valor de la variable VF (valor final) y la celda B9 el valor de la variable TIPO. Escribir la fórmula en Excel correspondiente a esta expresión en la celda B11.

Forma 1: en B11: =B5*B6*(1+B6)^B7/((1+B6)^B7-1)

Forma 2: =B5*(B6*(1+B6)^B7)/((1+B6)^B7-1)

Forma 3: =B5*(B6*(1+B6)^B7/((1+B6)^B7-1))

Forma 4: =B5*((B6*(1+B6)^B7)/((1+B6)^B7-1))

Ingrese la primera forma de la fórmula anterior en la celda B11 y asígnele los valores que se indican a cada una de las variables ¿ Qué valor calcula y asigna Excel a la celda B11 ?

VA = 2.000.000, T = 1,5%, N = 72, VF = 0, TIPO = 0, se obtiene el resultado B11 = 45.615,58

Para observar que la única diferencia que hay entre estas 4 formas de escribir correctamente la fórmula en Excel de la expresión matemática es el orden en que se realizan los cálculos, se deben ingresar en una hoja de Excel las cuatro fórmulas, en diferentes celdas, donde se observa que el resultado es el mismo; y para observar cómo realiza los cálculos el Excel, se utiliza la opción Evaluar Formula, siguiendo el siguiente procedimiento:

- Seleccionar la celda donde está la fórmula

- Luego abrir el menú Herramientas

- Seleccionar la opción Auditoría de fórmulas

- Del nuevo menú que se presenta seleccionar Evaluar fórmula

- Para que se vaya realizando cada operación paso a paso presionar el botón Evaluar

- Para finalizar presionar el botón Cerrar

Microsoft Excel incorpora, como una de sus funciones, para el cálculo del monto de la cuota para el pago de un préstamo, la función PAGO (Calcula el pago de un préstamo basándose en pagos constantes y en una tasa de interés constante), cuya sintaxis es la siguiente:

PAGO(tasa;nper;va;[vf];[tipo])

tasa es el tipo de interés del préstamo.

Nper es el número total de pagos del préstamo.

Va es el valor actual o lo que vale ahora la cantidad total de una serie de pagos futuros, también
se conoce como el principal.

Vf es el valor futuro o un saldo en efectivo que desea lograr después de efectuar el último pago.
Si el argumento vf se omite, se asume que el valor es 0 (es decir, el valor futuro de un
préstamo es 0).

Tipo es el número 0 (cero) ó 1 e indica el vencimiento de los pagos, 0 u omitido para el pago al
final del período y 1 para pago al final del período.

Ingrese en la celda B20 la función de Excel que calcula la cuota postpagable, el 5º argumento correspondiente a tipo es igual a 0 (cero). En la celda B20: =PAGO(B6;B7;B5;B8;B9), la cual produce como resultado = (Bs. 45.615,58).

Cómo este es el de una función financiera, Excel le asocia el formato moneda indicado por las siglas Bs. antes del valor numérico, además, este valor se muestra como negativo. Si el resultado se necesita que aparezca como positivo, se podría utilizar uno de los cambios siguientes:

En la celda B20: =-PAGO(B6;B7;B5;B8;B9)

En la celda B20: =PAGO(B6;B7;-B5;B8;B9)

Utilizar la misna fórmula para calcular el valor de la cuota postpagable para los siguientes valores:

VA = 500.000, T = 2,0%, N = 24, VF = 0, TIPO = 0

Para realizar el nuevo cálculo de la cuota postpagable no es necesario hacerle ningún cambio a la fórmula, sólo se requiere introducir los nuevos valores en las celdas respectivas. Con los valores indicados el nuevo resultado es entonces: B11 = 26.435,55

d. La expresión matemática para el cálculo del monto de la cuota prepagable de un préstamo (la cuota se paga al comienzo del período), viene dada como:

Escriba la fórmula correspondiente a esta expresión matemática e indicar el orden cómo se debe evaluar dicha fórmula para calcular el valor de la cuota prepagable:

Forma 1: VA*T*(1+T)^N/((1+T)^(N+1)-(1+T)) (forma básica)

8 9 1 7 10 2 5 3 6 4

Forma 2: VA*(T*(1+T)^N)/((1+T)^(N+1)-(1+T))

9 3 1 2 10 4 7 5 8 6

Forma 3: VA*((T*(1+T)^N)/((1+T)^(N+1)-(1+T)))

10 3 1 2 9 4 7 5 8 6

Si la celda D5 contiene el valor de la variable VA (valor actual), la celda D6 el valor de la variable T (tasa de interés), la celda D7 el valor de la variable N (número períodos), la celda D8 el valor de la variable VF (valor final) y la celda D9 el valor de la variable TIPO. Escriba la fórmula en Excel correspondiente a esta expresión en la celda D11.

Forma 1: =D5*D6*(1+D6)^D7/((1+D6)^(D7+1)-(1+D6))

Forma 2: =D5*(D6*(1+D6)^D7)/((1+D6)^(D7+1)-(1+D6))

Forma 3: =D5*((D6*(1+D6)^D7)/((1+D6)^(D7+1)-(1+D6)))

Ingrese la primera forma de la fórmula anterior en la celda D11 y asígnele los valores que se indican a cada una de las variables ¿ Qué valor calcula y asigna Excel a la celda D11 ?

VA = 2.000.000, T = 1,5%, N = 72, VF = 0, TIPO = 1 se obtiene el resultado D11 = 44.941,46

Ingrese en la celda D20 la función de Excel que calcula la cuota prepagable, el 5º argumento correspondiente a tipo es igual a 1. En la celda D20: =PAGO(B6;B7;B5;B8;B9), la cual produce como resultado = (Bs. 44.941,46)

Utilizar la misna fórmula para calcular el valor de la cuota prepagable para los siguientes valores:

VA = 500.000, T = 2,0%, N = 24, VF = 0, TIPO = 1

Para realizar el nuevo cálculo de la cuota prepagable no es necesario hacerle ningún cambio a la fórmula, sólo se requiere introducir los nuevos valores en las celdas respectivas. Con los valores indicados el nuevo resultado es entonces: D11 = 25.917,20

e. Escribir en la celda D5 una fórmula que calcule la sumatoria de los valores que están en el rango D1 hasta M1, dividido entre el promedio de los valores que se encuentran en el rango D3 hasta M3, y multiplique el resultado anterior por la raíz cuadrada del mayor valor que se encuentre en el rango D1 hasta M1.

La Expresión matemática de este problema podría ser escrita como:

Si se ingresan los valores requeridos en una hoja de cálculo de Excel en los rangos indicados, entonces en la celda D5 se ingresa puede ingresar la siguiente fórmula:

En D5: =SUMA(D1:M1)/PROMEDIO(D3:M3)*RAIZ(MAX(D1:M1))

f. Escribir en la celda C20 una fórmula que promedie los valores que están en las celdas C1 hasta C18, multiplique este resultado por la sumatoria de los valores que se encuentra en la celda D1 hasta D18, y dividida todo entre la raíz cuadrada de la suma de los valores que se encuentra en la celda D1 hasta D18 .

En C20: =PROMEDIO(C1:C18)*SUMA(D1:D18)/RAIZ(SUMA(D1:D18))