by Ilker | Jul 27, 2018 | Excel Tips & Tricks
Although Excel has a function to get week number of a date, WEEKNUM; there isn’t any to make reverse calculation. This method will help you answer the question – what week of the year is it, and give you the end date of each week. Syntax =MIN(DATE(current...
by Ilker | Jul 27, 2018 | Excel Tips & Tricks
Although Excel has a function to get week number of a date, WEEKNUM; there isn’t any to make reverse calculation. This method will help you answer the question – what week of the year is it, and give you the start date of each week. Syntax...
by Ilker | Jul 27, 2018 | Excel Tips & Tricks
Managing and organizing data doesn’t have to be a daily routine. You can automated tasks like finding the outliers in your data. In this article, we’re going to show you how to find unique values Excel. Syntax { =IFERROR( INDEX( data, MATCH( 0, ...
by Ilker | Jul 27, 2018 | 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 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 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 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 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 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...
