+ Reply to Thread
Results 1 to 14 of 14

Nesting IF functions using EOMONTH

  1. #1
    Registered User
    Join Date
    09-30-2013
    Location
    england
    MS-Off Ver
    Excel 2020
    Posts
    58

    Nesting IF functions using EOMONTH

    Hi Everyone,

    I need to create an 'IF' function that nests four EOMONTH functions.

    The following throws an error:

    =IF(D12="1st month goes here",EOMONTH(D7,2),if(D12="2nd month goes here",EOMONTH(D7,3),if(D12="3rd month goes here",EOMONTH(D7,5)))

    EOMONTH will increase the date in the resultant cell accordingly.

    Thanks in advance for your help.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Nesting IF functions using EOMONTH

    Add 1 more closing bracket ")"at the end of the formula and every thing seems OK to me.
    Quang PT

  3. #3
    Forum Contributor thameem127's Avatar
    Join Date
    04-06-2012
    Location
    Jeddah,Saudi Arabia
    MS-Off Ver
    Excel 2003,Excel 2007
    Posts
    321

    Re: Nesting IF functions using EOMONTH

    Attach a file

  4. #4
    Registered User
    Join Date
    09-30-2013
    Location
    england
    MS-Off Ver
    Excel 2020
    Posts
    58

    Re: Nesting IF functions using EOMONTH

    Hi
    Thaks for the reply
    here is the file..
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-30-2013
    Location
    england
    MS-Off Ver
    Excel 2020
    Posts
    58

    Re: Nesting IF functions using EOMONTH

    It returns the word 'FALSE' and not the date as desired but thanks anyway.

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Nesting IF functions using EOMONTH

    apart from the fact you have no named ranges in the example pasting in HSC_L2,HSC_L3 or HSC_L5
    gives
    30/04/2014
    31/05/2014
    or
    31/07/2014
    respectively
    if you don't have an option in d12 you will get false or if d12 isnt an exact match for what's in the dropdown
    =IF(D12="HSC_L2",EOMONTH(D7,2),IF(D12="HSC_L3",EOMONTH(D7,3),IF(D12="HSC_L5",EOMONTH(D7,5),"what to do if false")))
    Last edited by martindwilson; 02-17-2014 at 11:29 AM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Nesting IF functions using EOMONTH

    So you can not input anything in D12?
    There is a data validation for D12. Just come into Data menu / Data validation and remove it.

  8. #8
    Forum Contributor thameem127's Avatar
    Join Date
    04-06-2012
    Location
    Jeddah,Saudi Arabia
    MS-Off Ver
    Excel 2003,Excel 2007
    Posts
    321

    Re: Nesting IF functions using EOMONTH

    Are you looking for lookup function?

  9. #9
    Registered User
    Join Date
    09-30-2013
    Location
    england
    MS-Off Ver
    Excel 2020
    Posts
    58

    Re: Nesting IF functions using EOMONTH

    Hi

    It just returns the word 'FALSE' and not the desired date.

    it works fine on a singular EOMONTH but not the nested one.

    maybe you can't nest them!

    see attached with updated ranges.
    Attached Files Attached Files

  10. #10
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Nesting IF functions using EOMONTH

    your dropdown doesn't have the underscore that is in the formula
    HSC L3 is not the same as HSC_L3 so it wont match
    ive changed the dropdown values in the red cells to match the formula,you could do it the otherway around and change the formula to
    =IF(D12="HSC L2",EOMONTH(D7,2),IF(D12="HSC L3",EOMONTH(D7,3),IF(D12="HSC L5",EOMONTH(D7,5),"what to do if false")))
    Attached Files Attached Files
    Last edited by martindwilson; 02-17-2014 at 11:53 AM.

  11. #11
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Nesting IF functions using EOMONTH

    I created a Data Validation list from C20:C22 and named it Care. I then removed the underscores from your formula so that the values in C20:c22 would match the values in your formula....seems to work.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  12. #12
    Registered User
    Join Date
    09-30-2013
    Location
    england
    MS-Off Ver
    Excel 2020
    Posts
    58

    Re: Nesting IF functions using EOMONTH

    Hi

    it was the underscores afterall, thanks very much for your help.

  13. #13
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Nesting IF functions using EOMONTH

    You're welcome.

    Thank you for the feedback.

  14. #14
    Forum Contributor thameem127's Avatar
    Join Date
    04-06-2012
    Location
    Jeddah,Saudi Arabia
    MS-Off Ver
    Excel 2003,Excel 2007
    Posts
    321

    Re: Nesting IF functions using EOMONTH

    Check attached file and let me know if it is ok.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. #n/a data lost with XIRR, XNPV, EOMONTH functions
    By rybenn1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-10-2007, 11:36 AM
  2. Nesting functions in the functions dialog box
    By cs170a in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 04:05 AM
  3. Nesting functions in the functions dialog box
    By cs170a in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  4. [SOLVED] Nesting functions in the functions dialog box
    By cs170a in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  5. [SOLVED] Nesting functions in the functions dialog box
    By cs170a in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1