+ Reply to Thread
Results 1 to 13 of 13

ROUNDUP or ROUNDDOWN based on cell value

  1. #1
    Forum Contributor
    Join Date
    11-20-2014
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    177

    ROUNDUP or ROUNDDOWN based on cell value

    Hey,

    I made a small example, see attached.
    For that specific date I got 3 results (as a result of a formula which is not in there for now).

    The parameters are different for each country and can change at any time. So for NL the result has to be ROUNDUP and for DE it has to be ROUNDDOWN.

    How do I built that in the formule?
    Attached Files Attached Files
    Last edited by Gaellus; 05-24-2017 at 08:34 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    04-27-2015
    Location
    Abu Dhabi, U.A.E
    MS-Off Ver
    Office 365 | 2016
    Posts
    696

    Re: ROUNDUP or ROUNDDOWN based on cell value

    If(d$4="de",rounddown(d5,0),roundup(d5,0))

  3. #3
    Forum Contributor
    Join Date
    11-20-2014
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    177

    Re: ROUNDUP or ROUNDDOWN based on cell value

    Thats the problem. I can not put rounddown in the formule because it can change to roundup. It should refer to the cell with the text ROUNDUP or ROUNDDOWN in the formule

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: ROUNDUP or ROUNDDOWN based on cell value

    You cannot have a value in D5 and a formula: if you want the value in D5 (etc) to be ROUNDed then you will need VBA to do this,

  5. #5
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: ROUNDUP or ROUNDDOWN based on cell value

    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Replace both occurrences of D5 with your existing formula.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  6. #6
    Forum Contributor
    Join Date
    11-20-2014
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    177

    Re: ROUNDUP or ROUNDDOWN based on cell value

    Does not work unfortunately. Get a #REF error

    I think the fault occurs somewhere rounding a formule

    Please Login or Register  to view this content.
    Last edited by Gaellus; 05-24-2017 at 08:09 AM.

  7. #7
    Forum Contributor
    Join Date
    11-20-2014
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    177

    Re: ROUNDUP or ROUNDDOWN based on cell value

    new example file
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: ROUNDUP or ROUNDDOWN based on cell value

    Guessing ...

    =IF(INDEX($AP$12:$AP$14;MATCH($D$4;$AO$12:$AO$14;0))="ROUNDUP";ROUNDUP(DaytoDay!AV5*UZBsplitECOM!D5;0);ROUNDDOWN(DaytoDay!AV5*UZBsplitECOM!D5;0))

  9. #9
    Forum Contributor
    Join Date
    11-20-2014
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    177

    Re: ROUNDUP or ROUNDDOWN based on cell value

    Oh my god. Yeah those cells where merged before that why the array was AP till AR ...
    Silly mistake.

    But thanks alot. Got it solved now!

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: ROUNDUP or ROUNDDOWN based on cell value

    In your last file ..

    =IF(INDEX($L$12:$L$14,MATCH($D$4,$K$12:$K$14,0))="ROUNDUP",ROUNDUP(SUM(DaytoDay!AV5*UZBsplitECOM!D5),0),ROUNDDOWN(SUM(DaytoDay!AV5*UZBsplitECOM!D5),0))

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: ROUNDUP or ROUNDDOWN based on cell value

    Lesson: do not use merged cells!!!

  12. #12
    Forum Contributor
    Join Date
    11-20-2014
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    177

    Re: ROUNDUP or ROUNDDOWN based on cell value

    haha yeah good lesson

  13. #13
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: ROUNDUP or ROUNDDOWN based on cell value

    Quote Originally Posted by JohnTopley View Post
    Lesson: do not use merged cells!!!
    Quote Originally Posted by Gaellus View Post
    haha yeah good lesson
    I blogged about this, some time ago:

    http://excel.solutions/2016/10/using...ctively-excel/

+ 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 to roundup o rounddown depending on value
    By JC25 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-08-2013, 08:42 AM
  2. [SOLVED] Round, Roundup, Rounddown or something else????
    By PERE in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-11-2013, 09:14 AM
  3. [SOLVED] ROUNDDOWN & ROUNDUP in same formula?
    By Grasshopper Green in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-06-2013, 08:32 AM
  4. [SOLVED] Rounddown, roundup
    By allan223 in forum Excel General
    Replies: 3
    Last Post: 09-13-2012, 09:44 PM
  5. ROUNDUP and ROUNDDOWN function
    By james.kilgore in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 01-06-2011, 03:50 PM
  6. Excel 2007 : ROUNDUp / ROUNDDOWN
    By johannes in forum Excel General
    Replies: 1
    Last Post: 03-09-2010, 09:59 PM
  7. [SOLVED] Roundup or rounddown
    By Heather in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-14-2005, 10:40 PM
  8. [SOLVED] Roundup / Rounddown function
    By GreenMonster in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-14-2005, 05:05 PM

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