answersLogoWhite

0

It really depends on what it is you are trying to do. If you have a combined set of multiple conditions, using IF functions may work. IF functions may be nested, meaning that another IF function could form part of the True or False values. As an overall example, if in the cell B3 you had a value which must be multiplied by 10 if it is over 100 or else multiplied by 5 if greater than 50 or by 3 if less than or equal to 50, we will then as part of the False one IF value have to evaluate if B3 is over 50 or not, having already discovered that it is less than 100. The function will include a nested IF function as its False value which must include the same 3 component parts as any IF function:

=IF(B3>100, B3*10, IF(B3>50, B3*5, B3*3) )

Our Condition and True values are the same as a normal IF, but our False value is itself an IF function having a Condition, True and False values. Note that we also have 2 brackets at the end to close the 2 IF functions. The final bracket corresponds to the opening of the first IF.

When using multiple IF functions you will always have one less IF than the amount of conditions. In the example given we had 3 possible categories, but the formula only required 2 IF functions. Every IF eliminates one possibility. When the second last possibility has been eliminated, as there is only 1 remaining possibility left, it does not need a further IF function to test if it is True. The final possibility never needs to be tested for. In our example we had 3 possibilities. If our value is not over 100 and not over 50 then it must be less than 50, so we do not need to check for that possibility.

IF functions may be nested quite deeply at times and require careful thought at all times. It is easy to make a mistake, so they should be thoroughly tested. It is not a good idea to nest too many IF functions. In some cases, using another method might be more appropriate, such as using VLOOKUP. It really depends on what it is that you are trying to achieve, so each case is different.

User Avatar

Wiki User

8y ago

What else can I help you with?

Related Questions

When was Sum ifs introduced to Excel 2007?

The SUMIFS function first appeared in Excel 2007. The previous version, Excel 2003, did not have it.


What following functions was new in Excel 2007 sum ifs sum if count if or sum?

SUMIFS was new in Excel 2007.


What does Excel use to separate multiple arguments in a function?

Commas are used to separate arguments in Excel functions.


How do you replace 2011 with 2012 in multiple columns in excel?

Use Find and Replace.


Excel import multiple excel files software with license?

Use Asap Utilities, is free for personal use and, beside many other things, you can easily import many files in one.


How do you fill in multiple excel cells with the same information?

Use the "fill handle", to fill down or across.


How do you use Batch File in Microsoft Excel to change the file names of multiple files?

click file, then preferences, next click multiple then enter


Can you have more than 10 nested ifs in if function?

Prior to Excel 2007, the maximum was 7. From Excel 2007, the limit has been raised to 64. However, it becomes very impractical using too many nested IFs, so it is better to look for other ways where it is possible, like breaking down your formulas or using a lookup function, like VLOOKUP.


How can you use the words ifs in a sentence?

Mom to child: "Just do what I said, and I don't want to hear any ifs, ands, or buts from you!"


What is the plural of if?

The plural of if is ifs. As in "no ifs and no buts".


Where can you get multiple range test in excel?

50,70,80,50,60,44,70,50,70,60,50,50


What is the definition for multiple data series in Excel?

ametym a