Fresh Excel Tips

A worksheet contains time values including hours, minutes, and seconds as shown in the picture below. The task is to round the minutes to whole minutes by using the standard ROUND function. Note that a day has 24 hours, which is 1440 minutes. To round different time values to whole minutes: In cells A2:A10 list some [...]

When using the Filter option in the Data menu, it is recommended that the COUNT and COUNTA functions not be used because in a filtered worksheet they count both visible and hidden rows. Instead, use the SUBTOTAL function to get the subtotal of a list or database that is visible. The syntax for the SUBTOTAL function [...]

To count all cells that contain text data, use a combination of functions in one formula. The number of cells with any kind of data is counted with the COUNTA function. All numeric cells are counted with the COUNT function. Just subtract the results of the COUNT function from the results of the COUNTA function, [...]

As illustrated earlier, it is easy to find out the highest value in a list. But how do you find the one person on a sales team who sold the most? And how do you find out how much ahead of the others he or she is? Start with the LARGE function to determine the [...]

To determine the highest value in a list, we used the MAX function. To find out multiple high values of a range, the LARGE function can be used. This function returns the nth highest value in a set of data. LARGE(array, n) array: Array or range of numerical data in which we want to find the nth [...]

To determine the smallest value in a list, we can use the MIN function. However, the easiest way to find multiple small values of a range is by using the SMALL function. This function returns the nth smallest value in a set of data. Here is the syntax that is used for the SMALL Function: SMALL(array, [...]

In a company, employee sales are monitored. Columns B to E contain the sales for the first four months of the year. To determine which employee has the lowest monthly sales, use the MIN function. The function’s return value is the smallest value in a set. MIN(number1, number2, …) number1, number2, …: From 1 to 30 numbers for [...]

This example finds the largest value in the range A3:D11 by using the MAX function. The function’s return value is the largest value in a set. Here is the syntax for using the MAX Function MAX(number1, number2, …) number1, number2, …: From 1 to 30 numbers for which you want to find the largest value. It is [...]

In this example, a text string has to be converted to a valid time. Columns A and C contain different start and end times as part of a standardized text string. It is possible to extract the times and convert them to valid time values that can be used as the basis for calculations. To [...]

To calculate the difference between two dates, use the YEARFRAC function from the Analysis ToolPak add-in. This function calculates the fraction of the year represented by the number of whole days between start_date and end_date. The syntax is: YEARFRAC(start_date, end_date, basis) start_date: The start date. end_date: The end date. basis: Count basis to use. 0 or omitted = U.S. [...]