+ Reply to Thread
Results 1 to 10 of 10

use MROUND in same cell as formula with IF, AND, & SUM

  1. 11-25-2018, 04:27 AM #1
    Phierry
    Phierry is offline
    Registered User
    Join Date
    11-25-2018
    Location
    Gingin, Western Australia
    MS-Off Ver
    Windows 10
    Posts
    5

    use MROUND in same cell as formula with IF, AND, & SUM

    I want to know if I can use the MROUND formula in the same cell containing a complex formula made up with IF, AND, SUM.

    The formula is =IF(AND($D25<>"",$E25<>"",$F25<>""),($S24+SUM($M25:$R25)),"") where D, E and F contain meter readings if not blank, proceed with calculating the present usage, averages etc. Colm $S24 is the last invoice total, (in credit) which is added to the six rates and values in columns $M25:$R25. I need to round the final total to the nearest 5c but at present I can't work out if it is possible and if so, what would be the correct syntax.

    For now, I have inserted another column (T) just for the MROUND formula of the adjoining column and hid S

    I could not find an answer on the net, and would like to know if it is possible to include MROUND in the invoice formula, or do I have to use the additional (dedicated) column just for the MROUND as I am presently doing?
    Last edited by Phierry; 11-25-2018 at 04:31 AM. Reason: define column names clearer
    Reply With QuoteRegister To Reply
  2. 11-25-2018, 04:44 AM #2
    BadlySpelledBuoy
    BadlySpelledBuoy is offline
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: use MROUND in same cell as formula with IF, AND, & SUM

    Not sure without a sample workbook and expected results, but perhaps:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    BSB
    Reply With QuoteRegister To Reply
  3. 11-25-2018, 10:17 PM #3
    Phierry
    Phierry is offline
    Registered User
    Join Date
    11-25-2018
    Location
    Gingin, Western Australia
    MS-Off Ver
    Windows 10
    Posts
    5

    Re: use MROUND in same cell as formula with IF, AND, & SUM

    Thanks for the info BadlySpelledBuoy, but that formula only provided the #NUM error. I have tried all manner of combinations like that, after seeing similar posts on using MROUND in the forums. Sadly, the only way to make it work, was to create a second column just for the MROUND function (with a few "IF <>"" etc included. That worked fine so suspect that an extra column (with the main column hidden) is the way to go.
    Reply With QuoteRegister To Reply
  4. 11-26-2018, 12:42 AM #4
    Phierry
    Phierry is offline
    Registered User
    Join Date
    11-25-2018
    Location
    Gingin, Western Australia
    MS-Off Ver
    Windows 10
    Posts
    5

    Re: use MROUND in same cell as formula with IF, AND, & SUM

    I feel rather silly, as the MROUND function did work when I realised I was dealing with a negative value, not a positive. By simply inserting a minus sign before the Rounding value (0.05 in my case) I was able to get the formula to work fine. If the end result changes from negative to positive, I will have to adjust the formula. I am most pleased. Thanks so much.
    Reply With QuoteRegister To Reply
  5. 11-26-2018, 01:18 AM #5
    GeoffW283
    GeoffW283 is offline
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: use MROUND in same cell as formula with IF, AND, & SUM

    Phierry - can the result of your formula (without the MROUND) ever be negative?
    Was the expected result negative when you tested BSB's formula?
    I ask because if the first parameter to MROUND is negative then it will return #NUM.

    Ah - sorry - an hour too late with this response!

    One way to update BSB's formula:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Last edited by GeoffW283; 11-26-2018 at 01:30 AM. Reason: Add formula to try to add some sort of value to this post
    Reply With QuoteRegister To Reply
  6. 11-26-2018, 04:11 AM #6
    Phierry
    Phierry is offline
    Registered User
    Join Date
    11-25-2018
    Location
    Gingin, Western Australia
    MS-Off Ver
    Windows 10
    Posts
    5

    Re: use MROUND in same cell as formula with IF, AND, & SUM

    Geoff, the spreadsheet is a record of electricity accounts, and the balance has been in credit for some time. I have copied the formula you provided and it works fine also. I am just having a bit of trouble understanding how the new formula can work (which it does) when the second MROUND does not have an "IF" in front. I need to find something on "If's for Dummies" Thanks for taking the time to give me the formula.
    Reply With QuoteRegister To Reply
  7. 11-26-2018, 11:54 AM #7
    GeoffW283
    GeoffW283 is offline
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: use MROUND in same cell as formula with IF, AND, & SUM

    The first parameter to an "IF" function is a logical test that evaluates to TRUE or FALSE. In this case the test is: "$S24+SUM($M25:$R25)>=0". It evaluates to TRUE if our final answer is positive and FALSE if the final answer is negative.

    The second parameter to the "IF" is what the cell is set to if the final answer is positive. In this case:
    "MROUND($S24+SUM($M25:$R25),+0.05)"

    The third parameter to the "IF" is what the cell is set to if the final answer is negative. In this case:
    "MROUND($S24+SUM($M25:$R25),-0.05)".

    I hope that's clear!
    Reply With QuoteRegister To Reply
  8. 11-26-2018, 08:05 PM #8
    Phierry
    Phierry is offline
    Registered User
    Join Date
    11-25-2018
    Location
    Gingin, Western Australia
    MS-Off Ver
    Windows 10
    Posts
    5

    Re: use MROUND in same cell as formula with IF, AND, & SUM

    thanks GeoffW. I think I have a grasp on it now. I did a test with positive values using this formula on another sheet (just to be sure) and it worked fine in both positive or negative result values. The first IF to ensure that the preliminary cells of D, E & F are not blank (TRUE), has its FALSE (blank cells) at the very end of the formula. That's what made it confusing for awhile. It is a nested statement and makes sense. Thanks so much.
    Last edited by Phierry; 11-26-2018 at 08:56 PM.
    Reply With QuoteRegister To Reply
  9. 11-27-2018, 01:01 AM #9
    GeoffW283
    GeoffW283 is offline
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: use MROUND in same cell as formula with IF, AND, & SUM

    Thanks - no problem! You can mark the thread as solved if you're all set.
    Reply With QuoteRegister To Reply
  10. 11-27-2018, 01:04 AM #10
    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
    80,647

    Re: use MROUND in same cell as formula with IF, AND, & SUM

    To mark the thread as solved, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    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
+ 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. [SOLVED] Keep formula cell blank when intial cell is empty =MROUND(A1,10)
    By Amdavadi in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-17-2018, 10:04 AM
  2. [SOLVED] MROUND in same cell being rounded
    By jfeenz in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-09-2016, 03:29 PM
  3. [SOLVED] Help Using MROUND in A Formula
    By gaspower in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-06-2015, 08:28 PM
  4. SUM + MROUND in same cell
    By GAPP in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-24-2015, 12:57 PM
  5. [SOLVED] MROUND & IF Formula Challenges
    By vheb in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-13-2014, 09:10 AM
  6. 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
  7. 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1