+ Reply to Thread
Results 1 to 11 of 11

If this then that, but if this then that, but if this then that, otherwise this

Hybrid View

Supersadie If this then that, but if... 06-22-2018, 09:02 AM
CK76 Re: If this then that, but if... 06-22-2018, 09:06 AM
Supersadie Re: If this then that, but if... 06-22-2018, 09:10 AM
Supersadie Re: If this then that, but if... 06-22-2018, 09:18 AM
AliGW Re: If this then that, but if... 06-22-2018, 09:24 AM
CK76 Re: If this then that, but if... 06-22-2018, 09:29 AM
Sam Capricci Re: If this then that, but if... 06-22-2018, 09:31 AM
Supersadie Re: If this then that, but if... 06-22-2018, 09:59 AM
Sam Capricci Re: If this then that, but if... 06-22-2018, 10:02 AM
Supersadie Re: If this then that, but if... 06-22-2018, 10:04 AM
Sam Capricci Re: If this then that, but if... 06-22-2018, 10:08 AM
  1. 06-22-2018, 09:02 AM #1
    Supersadie is offline
    Forum Contributor
    Join Date
    08-23-2010
    Location
    uk
    MS-Off Ver
    365 on Mac
    Posts
    146

    If this then that, but if this then that, but if this then that, otherwise this

    I'm in a proper tangle over a combination formula.

    If A1=1 or 4 or 7, then "1" but if a1=2 or 5 or 8, then "2", if A1=3 or 6 or 9, then "3", otherwise "x"

    I should be able to do it, but it's eluding me with "too many arguments" coming up...

    Many thanks, people
    Reply With QuoteRegister To Reply
  2. 06-22-2018, 09:06 AM #2
    CK76 is offline
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,973

    Re: If this then that, but if this then that, but if this then that, otherwise this

    Since you have Office 365, you should have SWITCH function available.

    Try...
    Formula: copy to clipboard 
    =SWITCH(TRUE,OR(A1=1,A1=4,A1=7),1,OR(A1=2,A1=5,A1=8),2,OR(A1=3,A1=6,A1=9),3,"x")
    Last edited by CK76; 06-22-2018 at 09:10 AM.
    "Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something."
    ― Robert A. Heinlein
    Reply With QuoteRegister To Reply
  3. 06-22-2018, 09:10 AM #3
    Supersadie is offline
    Forum Contributor
    Join Date
    08-23-2010
    Location
    uk
    MS-Off Ver
    365 on Mac
    Posts
    146

    Re: If this then that, but if this then that, but if this then that, otherwise this

    Interesting. Not come across 'switch'. Will see if it works. Thanks for opening another useful chapter in my learning curve.
    Best wishes.
    Reply With QuoteRegister To Reply
  4. 06-22-2018, 09:18 AM #4
    Supersadie is offline
    Forum Contributor
    Join Date
    08-23-2010
    Location
    uk
    MS-Off Ver
    365 on Mac
    Posts
    146

    Re: If this then that, but if this then that, but if this then that, otherwise this

    Yup. Like a dream. Thanks, CK76!
    Reply With QuoteRegister To Reply
  5. 06-22-2018, 09:24 AM #5
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,492

    Re: If this then that, but if this then that, but if this then that, otherwise this

    Bear in mind, however, that should you share the workbook with colleagues who are not running Office 365, it won't work for them.
    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. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  6. 06-22-2018, 09:29 AM #6
    CK76 is offline
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,973

    Re: If this then that, but if this then that, but if this then that, otherwise this

    If above case you can use...
    Formula: copy to clipboard 
    =IF((A1<10)*(A1>0),IF(MOD(A1,3)=0,3,IF(MOD(A1,3)=1,1,IF(MOD(A1,3)=2,2))),"x")


    Edit: Can be shortened to...
    Formula: copy to clipboard 
    =IF((A1<10)*(A1>0),IF(MOD(A1,3)=0,3,MOD(A1,3)),"x")
    Last edited by CK76; 06-22-2018 at 09:32 AM.
    Reply With QuoteRegister To Reply
  7. 06-22-2018, 09:31 AM #7
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,684

    Re: If this then that, but if this then that, but if this then that, otherwise this

    just a different version...
    =IF(OR(A1=1,A1=4,A1=7),1,IF(OR(A1=2,A1=5,A1=8),2,IF(OR(A1=3,A1=6,A1=9),3,"x")))

    edit: a slightly shorter version too...
    =IF(OR(A1={1,4,7}),1,IF(OR(A1={2,5,8}),2,IF(OR(A1={3,6,9}),3,"x")))
    just an FYI
    Last edited by Sam Capricci; 06-22-2018 at 10:00 AM.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci
    Reply With QuoteRegister To Reply
  8. 06-22-2018, 09:59 AM #8
    Supersadie is offline
    Forum Contributor
    Join Date
    08-23-2010
    Location
    uk
    MS-Off Ver
    365 on Mac
    Posts
    146

    Re: If this then that, but if this then that, but if this then that, otherwise this

    That's what I was struggling to do, Sambo Kid - I suspect it was my incorrect use of double quotes around numbers that might have upset the applecart.
    Many thanks.
    Reply With QuoteRegister To Reply
  9. 06-22-2018, 10:02 AM #9
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,684

    Re: If this then that, but if this then that, but if this then that, otherwise this

    well, double quotes around a number will make it a text so if the original reference (A1 for example) is numeric but the formula you use to refer to it is "1" it will change it to a text then not find it.
    check out my second formula I just posted, it is a little shorter.
    Reply With QuoteRegister To Reply
  10. 06-22-2018, 10:04 AM #10
    Supersadie is offline
    Forum Contributor
    Join Date
    08-23-2010
    Location
    uk
    MS-Off Ver
    365 on Mac
    Posts
    146

    Re: If this then that, but if this then that, but if this then that, otherwise this

    I like the curly brackets. I was sure I didn't have to do a1=this, a2=that into infinity! Helpful.
    Reply With QuoteRegister To Reply
  11. 06-22-2018, 10:08 AM #11
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,684

    Re: If this then that, but if this then that, but if this then that, otherwise this

    Glad I could help! AND thank you for the reputation!
    Last edited by Sam Capricci; 06-22-2018 at 10:29 AM.
    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)

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