Results 1 to 6 of 6

Thread: Formular Challenge

  1. #1
    Neophyte standardF's Avatar
    Join Date
    Jun 2019
    Posts
    3
    Articles
    0
    Excel Version
    2016

    Question Formular Challenge



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Dears, I'm trying to achieve round up based on 0.3 multiples so that if I sum up variables, the decimal values should be round up to 0.3 multiples and remainder values returned.
    Example
    A B C SUM Desired Output
    1.21 1.29 1.27 3.77 5.17

    The formular I used is not working. =ROUND(A59,0) + IF(MOD(A59,1)>=0.3,1,0)
    Response I got is 5 with no remainder value returned.
    Please help out gurus in the house

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    769
    Articles
    0
    Excel Version
    Excel 2010 64bit
    click or tab into the cell with the formula,
    from the Formulas Ribbon, Formula Auditing section, click Evaluate Formula,
    you can now step through the formula and see what Excel is working with and the result of each part,
    maybe you'll notice something other than what you expect.

  3. #3
    Neophyte standardF's Avatar
    Join Date
    Jun 2019
    Posts
    3
    Articles
    0
    Excel Version
    2016
    Thanks NoS but I can't find a way around this

  4. #4
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    769
    Articles
    0
    Excel Version
    Excel 2010 64bit
    If that 5.17 is really the Desired Output... I have no idea.
    If the Desired Output should be 3.9 then try
    =INT(D59) + CEILING(MOD(D59,1),0.3)

    edit: added this comment
    I do see a problem when the remainder is greater than .9
    hopefully one of the formula guys can assist with this issue.
    Last edited by NoS; 2019-06-19 at 06:39 PM.

  5. #5
    Neophyte standardF's Avatar
    Join Date
    Jun 2019
    Posts
    3
    Articles
    0
    Excel Version
    2016
    Thanks NoS. The desired output is 5.17.

    Gurus in the house please help out

  6. #6
    Seeker Kolyu's Avatar
    Join Date
    Oct 2018
    Location
    Sofia, Bulgaria
    Posts
    14
    Articles
    0
    Excel Version
    2007, 2010, 2013, 2016
    I don't understand the logic behind 5.17

    Can you explain how you sum 1.21+1.29+1.27 and receive 5.17?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •