+ Reply to Thread
Results 1 to 7 of 7

Wrestingling with the IF function, MOD and multiple conditions

  1. #1
    Registered User
    Join Date
    06-10-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    5

    Wrestingling with the IF function, MOD and multiple conditions

    Hi guys, I've been at this for well over an hour and google'd my butt off so I'm throwing myself at your mercy. I have a perfectly decent forumla - =IF(MOD($A4,B$2)=0,$A4/B$2,"---") - that as you can probably tell returns "---" when I get an undesirable result; my question is how in god's name do I add an additional condition so it outputs "---" when the result is >30 as well as 0? I've searched every tutorial and it makes sense but I cant seem to apply it to this?

    I may just be having one of those mornings
    Any help will be smothered with love and furry things

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Wrestingling with the IF function, MOD and multiple conditions

    Try:

    =IF(AND(MOD($A4,B$2)=0,MOD($A4,B$2)<=30)),$A4/B$2,"---")

  3. #3
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Wrestingling with the IF function, MOD and multiple conditions

    Sorry, I'll try that again, with my brain in ...

    =IF(AND(MOD($A4,B$2)=0,$A4/B$2<=30)),$A4/B$2,"---")

  4. #4
    Registered User
    Join Date
    06-10-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Wrestingling with the IF function, MOD and multiple conditions

    Doesn't seem to like it - just get the ever-helpful 'the formula you typed has an error' message. :/

  5. #5
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Wrestingling with the IF function, MOD and multiple conditions

    Sorry, I obviously need more coffee:

    =IF(AND(MOD($A4,B$2)=0,$A4/B$2<=30),$A4/B$2,"---")

  6. #6
    Registered User
    Join Date
    06-10-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Wrestingling with the IF function, MOD and multiple conditions

    You Sir, are a legend, a scholar, a gentleman and a king among men! If chocolate could be sent via the interweb I'd shove my bar of crunch into the usb port. Thankyou!


    Also, just noticed my typo in the thread title. How embarrassing.

  7. #7
    Registered User
    Join Date
    04-18-2012
    Location
    Long Island, NY
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Wrestingling with the IF function, MOD and multiple conditions

    Hi all, I am new to the forum and amazed at the camaraderie and fellowship in solving problems...very heartening! Thanks to all!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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