+ Reply to Thread
Results 1 to 6 of 6

help with if and round/mround functions

  1. #1
    Registered User
    Join Date
    07-18-2016
    Location
    North york ,canada
    MS-Off Ver
    2012
    Posts
    3

    Question help with if and round/mround functions

    hello team
    i am new on this thread and in excel i am a complete novice and trying to learn . My requirememnt is below
    Amount of Vitamins and Minerals < 1% 0
    Amount of Vitamins and Minerals ≥ 1% to < 2% all cases 2%
    Amount of Vitamins and Minerals ≥ 2% to ≤ 10% round to nearest multiple of 2 %
    Amount of Vitamins and Minerals >10% to ≤ 50% round to nearest multiple of 5 %
    Amount of Vitamins and Minerals >50% round to nearest multiple of 10%


    i tried
    =IF(M22<50.01,0),IF(0.01<=M22<0.02,0.02),IF(0.02<=M22<=0.1,MROUND(M22,0.02),IF(0.1<M22<=0.5,MROUND(M22,0.05),IF(M22>0.5,MROUND(M22,0.01))))

    but somehow i am getting value error

    can someone tell me where i am going wrong

  2. #2
    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. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,759

    Re: help with if and round/mround functions

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    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.

  3. #3
    Registered User
    Join Date
    07-18-2016
    Location
    North york ,canada
    MS-Off Ver
    2012
    Posts
    3

    Re: help with if and round/mround functions

    basically what i am trying to do is automatically calculating the daily recommended values in the food we eat based on what the GVT thinks we need.the values i added on the prior mail is where i failed to do the calculation. There is no sensitive infoirmation so far
    After i figure out the Canada part then i will move to the US section of it
    again the requirements for where it says #value is

    Amount of Vitamins and Minerals < 1% 0
    Amount of Vitamins and Minerals ≥ 1% to < 2% all cases 2%
    Amount of Vitamins and Minerals ≥ 2% to ≤ 10% round to nearest multiple of 2 %
    Amount of Vitamins and Minerals >10% to ≤ 50% round to nearest multiple of 5 %
    Amount of Vitamins and Minerals >50% round to nearest multiple of 10%

    as you see on the first 2 requirements no rounding necessary
    M22 is where my value is ( this might not match with excel file attached as i had copied it before sending earlier.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,952

    Re: help with if and round/mround functions

    Try pasting this formula in N22:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Let me know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    07-18-2016
    Location
    North york ,canada
    MS-Off Ver
    2012
    Posts
    3

    Re: help with if and round/mround functions

    Thanks Jete Mc : minor correction and this works like a charm

    =IF(K22<0.01,0,IF(AND(0.01<=K22,K22<0.02),0.02,IF(AND(0.02<=K22,K22<=0.1),MROUND(K22,0.02),IF(AND(0.1<K22,K22<=0.5),MROUND(K22,0.05),IF(K22>0.5,MROUND(K22,0.01))))))

    dont see when to comment this as solved

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,952

    Re: help with if and round/mround functions

    You're Welcome and thank you for the feedback. You can mark the thread 'Solve' using the thread tools link above your first post in the thread. I hope that you have a blessed day.

+ 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] Round Decimal Values using Formulas and/or Functions
    By pipoliveira in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-25-2015, 06:00 PM
  2. [SOLVED] Dim LastRow etc wrapped round my functions can’t make it work
    By markDuffy in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-07-2013, 08:26 AM
  3. [SOLVED] Nested IF / Round Functions in Price List
    By OxfordCat in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-07-2012, 03:12 PM
  4. Excel 2007 : [SOLVED] mround function won't round up
    By donkey1 in forum Excel General
    Replies: 2
    Last Post: 04-24-2012, 12:01 AM
  5. using functions to round up to a specified number
    By WasWodge in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 08-05-2010, 04:17 PM
  6. Round Functions
    By jhill in forum Excel General
    Replies: 1
    Last Post: 12-04-2007, 07:56 AM
  7. If and Round Functions
    By billykiller05 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-26-2006, 11:55 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