+ Reply to Thread
Results 1 to 4 of 4

MROUND & IF Formula Challenges

  1. #1
    Registered User
    Join Date
    11-13-2014
    Location
    Toronto, Canada
    MS-Off Ver
    2007 Office
    Posts
    2

    MROUND & IF Formula Challenges

    Good Day,

    I am having some difficulty figuring out my formula.

    This obviously works just fine:
    =IF('Inventory Sheet'!B54<0, "OVERSTOCK", 'Inventory Sheet'!B54)

    I then added MROUND. It works when the number being referenced is positive only. When there is a negative number, it does not work.
    =MROUND(IF('Inventory Sheet'!B54<0, "OVERSTOCK", 'Inventory Sheet'!B54),12)

    Here is the original formula on the 'inventory sheet' I am referencing:
    =IF(D54>C54,E54-(D54-(C54*0.25)),(E54-D54)+(C54*0.25))

    Here is the info you can use for each.
    C54 = 24
    D54 = 42
    E54 = 18

    It works however, if you put the numbers like this:

    C54 = 24
    D54 = 18
    E54 = 42

    I essentially want the number to round to the nearest 12 when it is positive, and if negative, state 'OVERSTOCK'.

    Any help is appreciated!
    Last edited by vheb; 11-13-2014 at 09:10 AM. Reason: Solved

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

    Re: MROUND & IF Formula Challenges

    you cant round text so it should be
    =IF('inventory sheet'!B54<0, "OVERSTOCK",MROUND( 'inventory sheet'!B54,12))
    "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

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: MROUND & IF Formula Challenges

    Try this...

    =IF('Inventory Sheet'!B54<0, "OVERSTOCK", MROUND('Inventory Sheet'!B54,12))


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  4. #4
    Registered User
    Join Date
    11-13-2014
    Location
    Toronto, Canada
    MS-Off Ver
    2007 Office
    Posts
    2

    Re: MROUND & IF Formula Challenges

    Amazing, thanks. Can't believe I gaped on that.

    Cheers,

+ 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. [SOLVED] Formula- Dependent lists Multiple challenges EXCEL
    By Itolond in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-14-2014, 11:56 PM
  2. challenges
    By amartin575 in forum Suggestions for Improvement
    Replies: 3
    Last Post: 04-23-2014, 11:39 PM
  3. I need a mround type formula that only rounds up to a max number.
    By Rlong1818 in forum Excel Formulas & Functions
    Replies: 21
    Last Post: 11-20-2013, 12:10 PM
  4. [SOLVED] building a complex formula with the IF statement and Mround function
    By echy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-04-2013, 12:24 PM
  5. MROUND formula
    By SaschaB in forum Excel General
    Replies: 2
    Last Post: 10-20-2011, 10:43 AM

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