Avoid Nested IF in Microsoft Excel

 

In case we are using nested IF formula & we all know nested IF takes a lot of time in calculating the result. In this article, we will learn how we can avoid using nested IF function.

 

Question: I have a task assigned by my manager to check / compare the max change in the last four years sales figures.

I tried using IF function along with MAX & MIN functions to get the result but I guess the formula I am using is a bit long & I want to avoid; so if there is any formula that can replace nested IF then it will be a great help for me.

Following is the snapshot of data we have; the numbers may mislead you but these all are in million

 

img1

 

  • The formula is needed to check the max change between two years data
  • The formula we are going to use is
  • =IF(AND((MAX(B2:C2)-MIN(B2:C2))>(MAX(C2:D2)-MIN(C2:D2)), (MAX(B2:C2)-MIN(B2:C2))>(MAX(D2:E2)-MIN(D2:E2))), MAX(B2:C2)-MIN(B2:C2), IF((MAX(C2:D2)-MIN(C2:D2))>(MAX(D2:E2)-MIN(D2:E2)), MAX(C2:D2)-MIN(C2:D2), MAX(D2:E2)-MIN(D2:E2)))

 

img2

 

  • There are two formulas we can use & both of them involve MAX, MIN & ABS functions
  • In cell F2, the formula is
  • =MAX(B2:E2)-MIN(B2:E2)

 

img3

 

  • The second formula is
  • =MAX(ABS(C2-B2),ABS(D2-C2),ABS(E2-D2))

 

img4

 

Conclusion: In this way, we can avoid using nested IF function & we can opt for other functions.

 

image 48

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 
 



One thought on “Avoid Nested IF in Microsoft Excel

  1. Hi,

    Honestly, didn’t crossed my mind the nested if solution.
    On the other hand it seems to me obviously the max-min solution.

    Good job. Keep on.

    Vândalo

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>