Diferencia de horas en negativo.

Explicaré hoy una posibilidad para poder trabajar con diferencias de horas con resultado negativo, que como todos sabemos por defecto nos devuelve un error. La explicación viene motivada por un correo de una usuaria del blog:

...Intento restar horas pero el problema está en resultados negativos: TIEMPO REQUERIDO TIEMPO ESTIMADO DIFERENCIA 0:49:00 5:00:00 =G2-F2 0:14:00 3:00:00 =G3-F3 71:30:00 23:00:00 ¿? ...

Realmente lo que intentamos calcular no tiene mucho sentido para Excel, evidentemente sí para nuestros cálculos. Lo que ocurre es que el formato de horas para Excel es justamente eso:

• una hora puede ir desde las 00:00 hasta las 24:00, que son las 24 horas del día, y si lo visualizas en formato decimal un número de 0 a 1.

De hecho, fijémonos en el valor que tienes en la celda de 71:30:00 veremos que en la barra de fórmula aparece 02/01/1900 23:30:00, ya que lógicamente las 71:30 debe pertenecer a las 23:30 de dentro de dos días.

Vemos como, efectivamente, para diferencias positivas obtenemos un resultado válido, mientras que para las diferencias con resultado negativo Excel noos devuelve un error en modo almohadillas #########.
Cómo vamos a resolver esta cuestión, pues con un condicional SI anidándole la función TEXTO para forzar un cambio en el formato de la hora:

=SI(C5< B5;"-"&TEXTO(B5-C5;"DD-hh:mm:ss");TEXTO(C5-B5;"DD-hh:mm:ss"))

Tiene especial importancia definir el formato, con la función TEXTO, del valor con "DD-hh:mm:ss" para poder determinar el número de días 'DD' entre las horas.

Analicemos el resultado, para calcular cuantas horas y minutos han transcurrido entre los tiempos estimados y requeridos, restamos estimado menos requerido, para el primer caso se requirió un tiempo de 49 minutos cuando se había estimado necesario emplear cinco horas, por tanto, la diferencia entre ambos ha sido de 4 horas y 11 minutos menos de tiemplo empleado respecto al estimado.
Interpretemos el tercer caso, tiempo requerido 71 horas y 30 minutos frente al estimado de 23 horas, el resultado de acuerdo a nuestra fórmula nos devuleve el resultado correcto, se han empleado en la tarea dos días y 30 minutos más.

Seguir leyendo...

Gráfico en Excel con Barras de error con desviación estándar.

Recientemente recibí una consulta de un usuario sobre la forma de incluir en nuestros gráficos las barras de error con desviación estándar.
Partiremos como siempre de una tabla de datos, sobre el cual hemos calculado su media y su desviación estándar:

• =PROMEDIO(rango)


• =DESVEST(rango)

El procedimiento veremos que será sencillo, pero ¿cómo deberemos interpretar esas barras de error con desviación estándar?.
Desde luego la elección de líneas de error requiere conocimientos estadísticos, por lo que he elegido 'Barras de error con desviación estándar' para una interpretación más sencilla.
En definitiva obtendremos una línea ficticia de la media, complementada por unas barras-intervalos que indican el alejamiento de la misma de una desviación estándar. De esta forma, se destacarán los datos de nuestra serie de valores que se alejan de la media de forma significativa.
Seguro que algún especialista en asuntos estadísticos podrá dar una mejor interpretación.

En lo que toca a Excel, cuáles son los pasos...
Como siempre que queremos construir un gráfico, por rapidez seleccionamos nuestro rango de datos (A1:B7), y después navegamos hasta Insertar > Gráficos > Columna > columna agrupada. Le damos un aspecto a nuestro gusto:

Seguir leyendo...

Filtro avanzado con los diez mejores.

Responderé a una cuestión que me ha resultado curiosa, ¿cómo, usando el filtro avanzado sobre una base de datos, puedo obtener un listado con los diez mejores:

...Tengo una duda grande... estoy usando FILTRO AVANZADO y me piden que con eso seleccione los 10 mejores ¿cómo hago? ¿hay alguna fórmula? Osea con AUTOFILTRO SE PUEDE pero no sé hacerlo con FILTRO AVANZADO...

La solución es muy sencilla, obviamente con el Autofiltro, pero qué función deberíamos incluir en el rango de criterios que replique el resultado del Autofiltro.
Partamos del siguiente listado, del que obtendremos los tres valores más altos (los tres mejores) aplicando el filtro avanzado:

El rango de criterios (en amarillo E1:E2) estará compuesto de la prueba lógica:
=C2>=K.ESIMO.MAYOR($C$2:$C$9;3)

Seguir leyendo...

VBA:Configurar un gráfico usando Botones de Opción.

Hace algunos días expliqué en que forma podíamos configurar nuestros gráficos empleando Celdas validadas para seleccionar la Serie a mostrar (ver entrada). En esta ocasión añadiremos una nueva funcionalidad a nuestra propuesta; y es que empleando los Botones de opción de los Controles ActiveX conseguiremos alternar el gráfico mostrado entre las distintas Series de las que partimos, así como elegir qué Tipo de Gráfico nos conviene más.
Para todo ello necesitamos un origen de datos y un gráfico inicial de partida ya creado, nombrado como "2 Gráfico"(por comodidad).
Veámoslo:

Explicaré ahora como agregar a nuestra Hoja de cálculo los diferentes Botones de Opción que necesitaremos:

Para incorporar estos cinco botones de opción de los Controles ActiveX deberemos navegar por:
Ficha Programador > Controles > Insertar > Controles ActiveX > Botón de Opción:

Seguir leyendo...

VBA: cómo generar una carpeta o directorio

Tiempo atrás me llegó una consulta de un usuario donde me pedía que comprobara un código en VBA cuya finalidad era crear una carpeta:

...Tenemos un código para crear una carpeta si no existe y da error. La verdad que es la primera vez que me enfrento a esto porque el error que da no tiene sentido: Set DIRECTORIO_COMPLETO = CreateObject("Scripting.FileSystemObject") If Not DIRECTORIO_COMPLETO.FolderExists(RUTA_COMPLETA) Then DIRECTORIO_COMPLETO.CreateFolder (RUTA_COMPLETA) End If El error lo da el create folder ya que dice que no puede encontrar la ruta. Sin embargo la ruta existe (Excepto el final) La RUTA_COMPLETA es algo asi como 'C:\PEDRO\15254\' Existe C:\PEDRO y debería crear 15254. ¿Está mal el código? (Es que en otras versiones si funcina, grrrr)...

Existen varias formas de crear carpetas/directorios e incluso archivos, una es la que planteaba el lector; sin embargo yo voy a explicar una distinta que genera menos errores. Se trata de emplear la función VBA Dir y la instrucción MkDir.
Plantearé un ejemplo sencillo con una macro donde hemos definido las variables necsarias, tales como la ruta o Path donde crear el directorio y el nombre que asignaremos a la carpeta o NombreCarpeta:

Nuestro código VBA a incluir en un módulo del Explorador del proyecto dentro del Editor de VBA es:

Sub GenerarCarpeta()
Dim Path As String, NombreCarpeta As String

Path = "C:\"
NombreCarpeta = "Archivos\Nueva"
'Verificamos si la carpeta existe ya...
If Dir(Path, vbDirectory) <> "" Then
'Comprueba que la carpeta no existe para crearla.
If Dir(Path & NombreCarpeta, vbDirectory) = "" Then MkDir Path & NombreCarpeta
'MkDir se emplea para crear un directorio/carpeta.
'Si no se especifica la unidad de disco, el directorio/carpeta se crea en la unidad actual.
End If

End Sub

Si ejecutamos dicha macro 'GenerarCarpeta comprobaremos como se crea dicho directorio en la ruta definida, así como el subdirectorio; igualmente si ya estuviera generado el primer nivel de directorio (que he llamado 'Archivos') añade el subdirectorio correspondiente.

Seguir leyendo...