by Ilker | Jan 30, 2019 | Excel Tips & Tricks
Nested IF statements are useful for creating more complicated scenarios in Excel. However, IFS and SWITCH formulas can work more efficiently when dealing with more than one condition. These formulas work a bit differently than the legacy IF function. Let’s start...
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 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 12, 2018 | Excel Tips & Tricks
It’s often a good idea to use drop down lists or combo boxes to limit user errors make the data more standardized. However, sometimes we want our drop downs to populate under some conditions. Here, you can find out how to create conditional Excel drop down using Data...
by Ilker | Jun 12, 2018 | Excel Tips & Tricks
How to lookup across two tables by a condition is a combination of the VLOOKUP and the IF functions. The IF function handles which table and VLOOKUP handles what to search for. Syntax =VLOOKUP(lookup value, IF(condition, first table if condition is TRUE, second table...
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 | Apr 2, 2018 | Excel Tips & Tricks
The IF function is great for creating a logic statement. But IF is an either/or argument, what if you need more alternatives? Combining several IF statements in a nested structure can help overcome this. Syntax =IF(first logical test, do something if first test is...
