by Ilker | Jun 5, 2018 | Excel Tips & Tricks
This article shows how to find smallest value in array using SMALL and IF functions in an array formula. Syntax {=SMALL(IF(criteria range = criteria, data range reference), nth)} Steps Start with =SMALL( function Continue with IF( Type the criteria range-criteria...
by Ilker | Jun 5, 2018 | Excel Tips & Tricks
This article shows how to find largest value in array using LARGE and IF functions in an array formula. Syntax {=LARGE(IF(criteria range = criteria, data range reference), nth)} Steps Start with =LARGE( function Continue with IF( to apply condition Type the criteria...
by Ilker | Jun 5, 2018 | Excel Tips & Tricks
This article shows you how to find nth smallest value in a data table by using the SMALL function. Syntax =SMALL(absolute data range reference, nth) Steps Start with =SMALL( function Select the range that contains the values $B$2:$G$7, Select the cell that contains...
by Ilker | Jun 5, 2018 | Excel Tips & Tricks
This article shows you that how to find nth largest value by using the LARGE function. Syntax =LARGE(absolute data range reference, nth) Steps Start with =LARGE( function Select the range that contains the values $B$2:$G$7, Select the cell that contains nth value or...
by Ilker | May 31, 2018 | Excel Tips & Tricks
This article shows how to count values by length based on their character count. This method can be helpful to check your data against invalid entries. It is also a good practice to use SUMPRODUCT and N functions together. Syntax =SUMPRODUCT(N(LEN(range)>character...
by Ilker | May 31, 2018 | Excel Tips & Tricks
This article show you how to count years between two dates using the DATEDIF function. Syntax =DATEDIF(start date, end date, “y”) Steps Use =DATEDIF( Select or type range reference that includes start date B3, Select or type range reference that includes...
by Ilker | May 31, 2018 | Excel Tips & Tricks
This article show you how to count months between two dates using DATEDIF function. Syntax =DATEDIF(start date, end date, “m”) Steps Use =DATEDIF( Select or type range reference that includes start date B3, Select or type range reference that includes end...
by Ilker | May 30, 2018 | Excel Tips & Tricks
Summing values by a condition may sound easy. If not, please see Crunch Data Even Faster with the SUMIFS Function article as well. You can easily sum values by condition(s) with SUMIF and SUMIFS functions; with a limitation. The sum range and criteria ranges should...
by Ilker | May 30, 2018 | Excel Tips & Tricks
To sum range of cells in the same worksheet is one of the basic concepts of Excel. What about summing values across sheets? In this article we will explain how to sum values across sheets. Syntax =SUM(first sheet : second sheet ! reference) Steps Start with =SUM( Type...
