#IF THEN FORMULA EXCEL 2010 HOW TO#

Information functionsĭistant relatives of the IF() function are the information functions. So the same result we achieved with the ISERROR() function above can be achieved with an IF() function as shown here:īut there are cases when you cannot pretest the formula parameters and in those cases the ISERROR() function can come in handy. It is both faster (in terms of CPU) and better programming practice. Note: It is better to use the IF() function to avoid an error then to use the ISERROR() function to catch an error. For instance, if you have a formula that might cause a division by zero error, you can use the IFERROR() function to catch this event and return a valid value, as shown below: It allows you return a valid value in case a formula returns an error. IFError() functionĪ close relative of the IF() function is the IFERROR() function. They can also be used to implement new functions that does not exist such as MAXIF() and MINIF().įor a more in depth discussion of array formulas, look here. So, if for example, you wished to average a range of numbers without including Zeros in the calculation, you needed to rely on an array formula:Īrray formulas can also be used to mimic the working of countifs(), sumifs() and the rest of the xxxxxifs() functions, that simply did not exist in Excel versions before 2007. In Excel versions prior to 2007, the formula AVERAGEIF() did not exist. These functions enable us to apply an aggregation function to a subset of rows where those rows meet several conditions.įor instance, we can use the SUMIFS() function to sum all the sales that were made in the January of 2001, with a single function…Īrray formulas might be the most advanced of the formula techniques and while we can’t hope to cover the topic of array formulas in this article, it is important to mention that combining the IF() function with array formulas is a powerful tool. The COUNTIFS() and SUMIFS() function (and the rest of the multiple conditions aggregate functions) were introduced in Excel 2007. It goes without saying that these conditional functions are very useful. This group of functions allows you to apply a range function such as SUM(), COUNT() OR AVERAGE() only to rows that meet a specific condition.įor instance, you can sum or count all the sales that were made during the year 2001 as shown below: Will return ‘THE MICE PLAY’ if A1 equals either ‘cat is away’ or ‘cat is busy’. The formula… =IF(OR(A1='CAT IS AWAY',A1='CAT IS BUSY'),'THE MICE PLAY') Is ten times easier to write/read then the corresponding nested IF() aboveĪnother extremely useful Boolean function is the OR() function. You can use an additional if function to create a more complex condition within your Excel formula.įor instance: =IF(A1>10,IF(A110,IF(A110,A110,A2<20,B1='HAS AMMO'),'FIRE!') This is the most basic type of ‘complex’ if() function. Here are 7 conditional techniques that can help you create even more robust and useful Excel formulas: 1. There is a lot more power in Excel formulas conditions than just the basic IF() function, though. You will find it present in almost any complex formula. It is a fundamental building-block of Excel formulas. The IF() function is one of Excel’s super functions. If you want to guest post on this blog, check out the guidelines here. Users of later versions of Excel do not need to follow this step.7 Excel ‘If’ Function Techniques You Need to Know Users of Excel 2019 will need to enter this formula as an array function by pressing CTRL + SHIFT + ENTER. This example will use the CONCAT and IF Functions in an array formula to create a text string of Player Names which relate to a Team value of Red =CONCAT(IF(C3:C8='Red',B3:B8,'' See a later section on how this example can be achieved in Google Sheets by using the TEXTJOIN Function instead. Google Sheets users also have access to the CONCAT Function, but unlike in Excel, it only allows two values or cell references to be joined together and does not allow inputs of cell ranges.

#IF THEN FORMULA EXCEL 2010 HOW TO#

See a later section in this tutorial for how to replicate this example in older versions of Excel. Our first example uses the CONCAT Function and so is not available to Excel users before Excel 2019.Users of Excel 2019+ have access to the CONCAT Function which is used to join multiple strings into a single string. This tutorial will demonstrate how to concatenate cell values based on criteria using the CONCAT Function in Excel and Google Sheets.

If then formula excel 2010