+ Reply to Thread
Results 1 to 9 of 9

Nesting If and test one cell for 2 conditions then apply a formula. Len test would work

  1. 07-10-2018, 05:23 PM #1
    JET2011
    JET2011 is offline
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    MS 365
    Posts
    225

    Nesting If and test one cell for 2 conditions then apply a formula. Len test would work

    I have this formula now that test for one condition and if true applies a formula m- it works. I would like to expand to test the one cell for two conditions but have had no luck. I tired AND and also LEN.

    Right now the formula tests for cell D2 > .5 would like also to check for D2 < 9.5. In actuality if it checked for cell LENth to be 1 would be more precise for my needs.


    HTML Code: 
    I tried this but did not work

    HTML Code: 
    Thanks for any direction, need to avoid using code. jjj
    Reply With QuoteRegister To Reply
  2. 07-10-2018, 05:27 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Nesting If and test one cell for 2 conditions then apply a formula. Len test would wor

    Did you try to add color to your post? If not, what is the purpose of including that in the 2nd formula?

    For your question though, try this...
    =IF(and(D2>0.5, D2<9.5),B2&";"&"0"&D2,B2)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 07-10-2018, 05:42 PM #3
    JET2011
    JET2011 is offline
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    MS 365
    Posts
    225

    Re: Nesting If and test one cell for 2 conditions then apply a formula. Len test would wor

    You can see the color code in the second formula somehow did not work but your formula did. Thanks a bunch. jjj
    Reply With QuoteRegister To Reply
  4. 07-10-2018, 05:43 PM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Nesting If and test one cell for 2 conditions then apply a formula. Len test would wor

    Good stuff, happy to help
    Reply With QuoteRegister To Reply
  5. 07-11-2018, 07:49 AM #5
    JET2011
    JET2011 is offline
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    MS 365
    Posts
    225

    Re: Nesting If and test one cell for 2 conditions then apply a formula. Len test would wor

    It turns out there are some situations where the current formula does not apply. I believe what will work for all needs is testing D2 on the basis of numbers of characters. Here I only want to apply the formula if the character length of D2 is 1. In other words for the below formula would like to replace the D2>0.5 with a check for D2 LEN = 1.

    =IF((D2>0.5),B2&";"&"0"&D2,B2)

    Thanks for any direction.
    Last edited by AliGW; 07-12-2018 at 08:23 AM.
    Reply With QuoteRegister To Reply
  6. 07-12-2018, 08:12 AM #6
    JET2011
    JET2011 is offline
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    MS 365
    Posts
    225

    Re: Nesting If and test one cell for 2 conditions then apply a formula. Len test would wor

    Still cannot test for charter length. I have worked with LEN in the past but not in such a way I'm trying to use it now.
    Reply With QuoteRegister To Reply
  7. 07-12-2018, 08:23 AM #7
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    81,090

    Re: Nesting If and test one cell for 2 conditions then apply a formula. Len test would wor

    This maybe:

    =IF(LEN(D2)=1,B2&";"&"0"&D2,B2)
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  8. 07-12-2018, 01:12 PM #8
    JET2011
    JET2011 is offline
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    MS 365
    Posts
    225

    Re: Nesting If and test one cell for 2 conditions then apply a formula. Len test would wor

    Thanks again - that works! Self-taught -impressive.

    -John
    Reply With QuoteRegister To Reply
  9. 07-12-2018, 01:15 PM #9
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    81,090

    Re: Nesting If and test one cell for 2 conditions then apply a formula. Len test would wor

    You're welcome!

    Yes, over many years ... And mostly from these forums.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Test two cells and set a third cell with different values depending on test results
    By Enrique Mosre in forum Excel General
    Replies: 3
    Last Post: 08-21-2013, 07:10 PM
  2. [SOLVED] Test if a cell is within a name range and return a text value based on the test
    By DraconR in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 06-24-2013, 02:46 AM
  3. Excel 2007 : HELP!! Need a formula to test for multiple conditions and bring back different values
    By Resh in forum Excel General
    Replies: 1
    Last Post: 02-14-2012, 03:41 AM
  4. Pull sub-test scores from test w/best overall test score (PSAT)
    By PowerSchoolDude in forum Excel General
    Replies: 0
    Last Post: 11-19-2009, 08:29 PM
  5. formula that can test multiple conditions
    By harryhu in forum Excel General
    Replies: 3
    Last Post: 04-07-2009, 05:54 AM
  6. [SOLVED] If formula to test several conditions
    By MIchel Khennafi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-01-2006, 07:10 PM
  7. Formula that will test text conditions in a single cell
    By Prohock in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-04-2006, 05:25 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1