by Ilker | Jun 11, 2018 | Excel Tips & Tricks
To sum by month has nothing different than How to SUM values between two dates using SUMIFS formula. However, this article shows you a more dynamic and specialized approach combining EOMONTH and SUMIFS functions that you don’t need to guess how many days in a...
by Ilker | Jun 11, 2018 | Excel Tips & Tricks
This article shows you how to find number of days in a month to make your month-driven calculations dynamic. DAY and EOMONTH functions help us for this tip. Syntax =DAY(EOMONTH(Date, 0)) Steps Start with =DAY( Continue with EOMONTH( Select or type the range that...
by Ilker | Jun 7, 2018 | Excel Tips & Tricks
This article shows how to find errors in a list by using ISERROR and MATCH functions in an array formula. Syntax { =MATCH(TRUE, ISERROR(search array), 0) } Steps Start with =MATCH( function Type TRUE, variable to search for the matched values Continue with ISERROR(...
by Ilker | Jun 7, 2018 | Excel Tips & Tricks
If your data is case sensitive, you may have issues finding correct matches with lookup functions. This How to make case sensitive match article shows you how to combine MATCH and EXACT functions to achieve case sensitive matching. Syntax { =MATCH(TRUE, EXACT(search...
by Ilker | Jun 7, 2018 | Excel Tips & Tricks
The tax brackets can be confusing to work with. Different ‘levels’ of your income needs to be multiplied with a different percentage to find the total. This can be done with the conventional Excel formulas like IF and a bunch of others, but that’s the long and hard to...
by Ilker | Jun 6, 2018 | Excel Tips & Tricks
The logical tests and conditional checks have important role in Excel models. Potential errors can be detected and handled by using the right logical tests. If you know How to check if a value exists in a list, you can use this logical statement to detect and...
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...
