Jump to content


Photo

Nesting many Excel IF functions together

formulas IF nesting CHOOSE

  • Please log in to reply
2 replies to this topic

#1 Rathin

Rathin

    Member

  • Members
  • PipPip
  • 16 posts
  • LocationWest Bengal, India

Posted 06 July 2014 - 03:51 AM

Dear Sir,

 

I have made a report sheet where I can see month wise comparative sales report for 10 years. I put the formula like - (if the validation is "Apr" then value should be "x"). But in this process I am not able to put the logic for 12 months.(error message is "you've entered too many arguments for this function"). So I have broke the validation in two parts. (1st 6 months in one and rest 6 months in another part). If it is possible to make this in one, it will help a lot. Awaiting for the help.

 

Thanks and regards

Rathin Das



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 888 posts

Posted 06 July 2014 - 09:19 AM

Hi Rathin,

 

Assuming you are using Excel 2007 or a more recent version, you should be able to create up to 64 nested IF functions.  It's possible that you're not using the IF function correctly, so you might want to revisit the video and e-book for Expert Skills Lesson 3-5: Use the IF logic function for a refresher.

 

You'll notice that this lesson recommends against using nested IF functions, and instead recommends placing single IF functions in many separate cells.  This often makes the formulas easier to work with.

 

An alternative that you might want to consider is the CHOOSE function.  This allows you to display various different values depending upon a single cell that contains a number. 

For example:

=CHOOSE(A1, "X", "Y", "Z")

...would look in cell A1 and display "X" if the number 1 was found, "Y" if the number 2 was found, and so on. 

 

For more about the CHOOSE function, try searching for it in the Insert Function dialog. 

For full instructions on opening the Insert Function dialog, see Expert Skills Lesson 3-3: Use the Insert Function dialog and the PMT function.

 

If you're still having trouble getting this to work, please attach an example workbook and I will try to provide a solution.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 Rathin

Rathin

    Member

  • Members
  • PipPip
  • 16 posts
  • LocationWest Bengal, India

Posted 10 July 2014 - 02:38 AM

Dear Sir,

 

Thank you for your assistance. Actually it was in excel 2003 previously. Now it is fine with excel 2007.

 

Regards

Rathin







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users