by Ilker | Jul 27, 2018 | Excel Formulas, Excel Tips & Tricks
It’s often a good idea to highlight empty cells in a data set to help easily identify the outliers. Here, you can find out how to highlight empty cells dynamically using Conditional Formatting. Steps Begin by selecting the data range B3:H10 Open the Conditional...
by Ilker | Jul 18, 2018 | Excel Formulas, Excel Tips & Tricks
When you need to calculate the difference between a time present day and the next day, calculations can get messed up and you will see lots of # characters. This article shows how to create a time calculator in Excel that can calculate shift time after midnight using...
by Ilker | Jul 18, 2018 | Excel Formulas, Excel Tips & Tricks
This article shows how to count unique values Excel formulas SUMPRODUCT and COUNTIF. The SUMPRODUCT function can handle arrays without actually using array formulas, and this allows evaluating the formulas in each cell of an array. Syntax =SUMPRODUCT( 1 / COUNTIF(...
by Ilker | Jul 17, 2018 | Excel Formulas, Excel Tips & Tricks
This article shows how to get last match by using MAX, MIN, IF and ROW functions. Excel’s array formula ability to evaluate formula for each cells in an array is key factor in this case. Syntax { =MAX( IF( data = search value, ROW( data ) – MIN( ROW( data...
by Ilker | Jul 17, 2018 | Excel Formulas, Excel Tips & Tricks
This article shows How to find closest match by using INDEX, MATCH, ABS and MIN functions. Excel’s array formula ability to evaluate formula for each cells in an array is key factor in this case. Syntax { =INDEX( return array, MATCH( MIN(ABS( search...
by Ilker | Jul 13, 2018 | Excel Formulas, Excel Tips & Tricks
You can sum tables, columns, but what how do you add up every third row? In this article, we’re going to show you how to sum every n rows using the SUM and the OFFSET functions. The OFFSET function’s ability to return range is the key in this case. Syntax...
by Ilker | Jul 13, 2018 | Excel Formulas, Excel Tips & Tricks
Calculating averages by week number is a bit different than doing this by month. Instead of finding start and end dates of date range, we focus on the exact week number using the WEEKNUM function. Syntax =AVERAGEIFS( range of values to calculate average, range of week...
by Ilker | Jul 13, 2018 | Excel Formulas, Excel Tips & Tricks
To count by week number is a bit different than by month. Instead of finding the start and end dates of the date range, we focus on exact week number using the WEEKNUM function. Syntax =COUNTIFS( range of week number helper column, current week number) Steps Add a...
by Ilker | Jul 13, 2018 | Excel Formulas, Excel Tips & Tricks
To sum by week number is a bit different than How to sum by month. Instead of finding start and end dates of date range, we focus on exact week number by using WEEKNUM function. Syntax =SUMIFS( range of values to sum, range of week number helper column, current week)...
