Foro Excel

Buenas tardes a todos.

Tengo una duda que no logro resolver y me gustaría que álguien me echara una mano, si fuera posible. Tengo los datos en una hoja llamada Vinyas que me da un interés para vencimientos mayores a 120 días. Ese valor, lo tengo que poner en otra hoja llamada suma de interés y cada día que pasa (ya que la hoja vinyas calcula el interés diario por sobrepasar el saldo mayor a 120 días) debe reflejarse ese importe, para posteriormente, hacer un sumatorio desde una fecha hasta la fecha de liquidación de intereses.

No sé si me he explicado bien pero os pongo la hoja excel para ver si se os ocurre algo para obtener esos importes diarios. Yo estoy bloqueado.

Gracias de antemano.

Hola, Thaiel

Estoy tratando de comprender el cálculo de tu consulta -asumo que es sólo un ejemplo- y hay algunos extremos que no 'pillo' demasiado bien.

1.- En el ejemplo que has puesto, veo que en las 'fechas valor' hay fechas 'futuras'. Si al final, se trata de tener un cálculo de días que sobrepase los 120, ya está bien; pero puede ser que, si está así en el 'original'. De hecho, según el ejemplo, sería los días 'negativos' los que están excediendo, no los 'positivos', puesto que éstos, al ser 'futuros', no pueden devengar interés alguno.

2.- El porcentaje de la celda [F8] (5%) es un interés anual, supongo (?). O hay que entender que es sobre la deuda contraída de la columna 'DEBE' [col. 'E'] (?)

3.- Sobre los datos acumulados: La observación que hago es que el saldo de días de la col [E], de 'fecha valor' es la que marca los días transcurridos. Pero el cálculo de la col. "Saldo" [col. 'G'] recoge datos acumulados, y no todos tienen >120 días respecto a la fecha valor, por lo que no veo si el cálculo del 'tipo de interés deudor' se aplica correctamente. ¿No debería afectar solamente los datos de 'fecha valor' que individualmente superen los 120 días? Si se trata de datos de un cliente ¿no sería mejor trabajar sobre el saldo medio ponderado por los días?

En fin... Si puedes darme una pista te lo agradeceré mucho. Si tienes datos históricso reales, pásalos; pero ocultando la 'identidad del deudor', que eso es ajeno al problemas tratado.

A ver si podemos echarte una mano. Al final, la respuesta de una fórmula podría ser hasta trivial; pero quizá no tenga en cuenta de si sirve para los objetivos propuestos que, en el fondo, podría ser de lo que se trata.

Un saludo

Hola Expelos. Gracias por tu respuesta.

No me he explicado bien y de ahí tus dudas. Voy a intentar explicártelo sobre la hoja ejemplo. Si miras hoy día 30, el interés mayor a 120 días habrá cambiado ya que es un interés diario por tener un saldo mayor de 120 días de esa cuenta (el interés del 5% es anual pero el que obtengo como "interés mayor 120 días" es diario por exceder una cantidad de dinero al pagar a más de 120 días). Ese interés diario, como verás, cambia dependiendo de la fecha, es decir, a medida que pasan los días, los días con vencimiento mayor a 120 días son menores, por lo tanto, el interés a pagar será menor....no sé si me explico. Básicamente funciona como el cálculo de un banco de una póliza de crédito.

Bien, lo que yo busco es que el importe que me dá cada día el interés mayor a 120 días, en lugar de mirar todos los días la tabla y el resultado que salga copiarlo en la hoja "suma interés", que automáticamente, me lo ponga al lado de la fecha que le corresponde, es decir, en el día 29-11 me saldría 37,84 €....hoy día 30-11 debería salir 37,20 €. Así me acumularía el saldo del interés desde el 29 hasta cuando se tuviera que liquidar la cuenta...y con una autosuma, tendría el importe a pagar.

No sé si ahora me has entendido, perdona si te he liado más. Si ves la hoja ejemplo, he puesto una fórmula, pero todos los días me la va a cambiar con el resultado que me da el interés mayor a 120 días para todos los días del mes o meses que yo lo arrastre....y yo quiero que me coincida el día 29-11 con el resultado que me dió la página vinyas para el día 29. El día 30, que es hoy sale otro resultado y ese debe aparecer en la hoja "suma interés" en el día 30-11. Así tendría dos importes...el 37,84 € del 29-11 y el 37,20 € de hoy. Si liquidara hoy la cuenta...debería pagar 75,04 € de ineterés por demorar el pago de un saldo acumulado más de 120 días.

Quizás el planteamiento de mi hoja excel no sea el correcto y por eso no doy con la tecla para obtener el resultado esperado.

De nuevo muchas gracias por tu ayuda.

Saludos.

Creo que sigo sin entender la forma de cálculo, pero interpreto lo que necesitas.
Aprovecho que en tu hoja inicial, 'Vinyas', la columna [A] está libre, y explico lo que hacen las fórmulas del adjunto.

Una solución es indexar (hacer de esta columna un índice) de manera que, si se cumple que en la celda contigua (de la col. [B]) hay una fecha Y() -es decir, que además- en la col. [J] hay un valor positivo (Saldo de números comerciales), acumule las veces que ocurren ambas condiciones. Esto se consigue con la fórmula:
Vinyas!A11 =SI(Y(ESNUMERO(B11);J11>0);MAX(A$10:A10)+1;"-")
De manera que siempre que se cumplan esas condiciones, sume una unidad al total anterior. Eso es un 'contador' de veces que se cumplen esas condiciones.

Arrastraremos esa fórmula hacia abajo, de manera que encuentre todas las coincidencias y las numere.

Vamos ahora a la hoja 'Suma Interés'
Usaremos las columnas [A, B y C], con los encabezamientos (en la fila [2]):
FECHA ->INTERÉS DEUDOR -> Acumulado
Ahora, en las celdas de la fila [3]:
A3=SI(FILAS(B$2:B2)>MAX(Vinyas!$A$11:$A$83);"";BUSCARV(FILAS(B$2:B2);Vinyas!$A$10:$B$83;2;0))
con SI(FILAS(B$2:B2)... Tenemos un contador de filas, y es importante el signo $, porque fija el inicio en la fila [2]
...>MAX(Vinyas!$A$11:$A$83) compara ese contador con el máximo contado de la hoja 'Vinyas'. Si lo supera, entonces, no debe poner nada: ...;""...
Pero si 'está en rango' (no supera el valor de recuento de 'Vinyas'), entonces:
...BUSCARV(FILAS(B$2:B2);Vinyas!$A$10:$B$83;2;0))
se ocupa de buscar el valor de 'filas' de la hoja 'Suma Interés' que coincida con el valor correspondiente de la hoja 'Vinyas', a lo largo del rango de ésta: Vinyas!$A$10:$B$83. Como ;2; apunta a la fecha, traerá en la col. [A] de 'Vinyas' la fecha en que hayan coincidido ambos contadores.
NOTA que ...;0)) obliga a Excel a encontrar la coincidencia EXACTA.

En la col. [B]:
B3=SI(ESNUMERO(A3);BUSCARV('Suma Interés'!A3;Vinyas!$B$11:$J$84;9;0);"-")
Calcula: si hay número (las fechas en Excel son números) en la celda [A3], entonces, busca el valor de 'Deudores' de la hoja 'Vinyas', correspondiente a ese día.

En la col [C]:
C3 =SI(ESNUMERO(A3);B3*Vinyas!$F$8;"-")
Simplemente 'calcula' los valores hallados de la celda [B3] y lo multiplica por el 5% de la celda [Vinyas!$F$8], siempre y cuando la celda [A3] contenga un número (fecha).

Ahora, arrastra hacia abajo [A3, B3 y C3], hasta que en la col. [A] empieces a ver el valor "-"

OBSERVACIÓN:
En la hoja 'Suma Interés' sólo tendrás aquellos valores representativos de que haya un saldo deudor, y no tiene en cuenta los otros, que quizá interese si el saldo deudor se ha cancelado anteriormente y luego vuelve a ser deudor. Para tener en cuenta este hecho, quizá convendría eliminar la condición del contador de la hoja 'Vinyas' y adaptarlo a que sólo contenga la primera de las dos condiciones, es decir, que haya una fecha con algún movimiento, sin que el saldo sea necesariamente deudor. En ese caso, un condicional debería calcular el 5% sólo en caso de saldos deudores, cada vez.

Espero que te sirva. Si no explico bien lo que hace cada fórmula, vuelve a preguntar...
Un saludo

xpelos, vaya currada!!!

Pero es más simple que todo eso, aunque esos cálculos me van a servir para otro tipo de información!!.

Yo sólo calculo el interés diario por tener esos importes pendientes de pago, es decir, por cada día que tenga ese saldo pendiente de pagar, voy a tener un interés que abonar....que es lo que me sale en interés > a 120 días. Evidentemente es un saldo deudor (de momento voy a eliminar los importes vencidos y que haya pagado y así, comprobarás que el interés > a 120 días disminuye).

Esa cantidad que me da cada día que abro la hoja excel y que el importe cambia o bien porque no tengo más movimientos de pago y sólo cambia el número de días que debo el dinero y baja el interés a pagar, o bien porque elimio el importe que debo, porque ya lo he pagado, con lo que el interés > a 120 días disminuirá en mayor medida que la anterior ya que el capital pendiente de pago ha bajado.

Ese importe diario....(si miras la hoja hoy tendrás un interés de 37,05 € y ayer era de 37,20 €). En la hoja suma de interés, yo quiero que me salga automáticamente al lado de la columna de las fechas el interés que me ha dado cada día, es decir, ayer día 30 debería de poner automáticamente en la columna B de la hoja "suma interés" 37,20 € al lado de la fecha 30-11....y hoy, debería aparecer 37,05 €.

No es un tema de cálculos...es un tema de que aparezca un importe de la hoja vinyas automáticamente...para que no tenga que ponerlo manualmente.

Te agradezco tu tiempo y tal y como has dejado la excel, me va a servir para otros informes que tengo en mente....pero busco que haya una coincidencia:

Hoja vinyas ---- fecha actual ( hoy() ), me da un interés mayor a 120 días = 37,05 €
Hoja Suma interés ----- fecha de la columna que coincida con la fecha ( hoy() ) debería aparecer automáticamente 37,05 €.

Si no se puede hacer para que salga de manera automática, pondré la cifra manualmente. Repito, es independiente de la manera de calcular ese importe.

Gracias de nuevo por tu tiempo.

Un saludo

Hola, Tahiel...

Creo que si enviaras el adjunto con comentarios sobre el mismo, me sería de una gran ayuda.

Un saludo, y hasta pronto!