• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Rounding and SUMPRODUCT Function

Michelle06

New Member
Hi,

I have used the SUMPRODUCT function to generate the answers that I need, however; I cannot work out how to add Rounding Functions. If the value generated from the SUMPRODUCT function includes a decimal that is equal to or greater than 0.33 (e.g 250.33) then I need that amount to round up to the nearest 1 (251). Otherwise, I need the amount to round down (250.32 to round down to 250). My current formula is:

=(SUMPRODUCT(--(C5>={356;396;494;712;1283;1539;3461}),(C5-{355;395;493;711;1282;1538;3460}),{0.19;0.1;-0.08;0.1377;-0.0027;0.045;0.1}))

Could anyone please point me in the right direction?

Thanks,
Michelle
 

r1c1

Administrator
Staff member
Interesting question @Michelle06

One option is to obviously add an extra condition by duplicating SUMPRODUCT, like this:

=INT((SUMPRODUCT(--(C6>={356;396;494;712;1283;1539;3461}),(C6-{355;395;493;711;1282;1538;3460}),{0.19;0.1;-0.08;0.1377;-0.0027;0.045;0.1})) + (MOD((SUMPRODUCT(--(C6>={356;396;494;712;1283;1539;3461}),(C6-{355;395;493;711;1282;1538;3460}),{0.19;0.1;-0.08;0.1377;-0.0027;0.045;0.1})),1)>0.33))

Of course, its hideously long. I am not able to think of a quick way to shorten this, but if I get any ideas, I will post here. I am sure some one smarter will come along and post something that does what you need with less cumbersome formula.
 

r1c1

Administrator
Staff member
finally woke up. Here is another one.

=ROUND((SUMPRODUCT(--(C5>={356;396;494;712;1283;1539;3461}),(C5-{355;395;493;711;1282;1538;3460}),{0.19;0.1;-0.08;0.1377;-0.0027;0.045;0.1}))+0.17,0)

Alternatively, the one suggested by @NARAYANK991 should work too.
 

Hui

Excel Ninja
Staff member
I'd suggest a User Defined Function

Code:
Function myRnd(Target As Double, Rnd As Double) As Double

If Target - Int(Target) >= Rnd Then
  myRnd = Int(Target) + 1
Else
  myRnd = Int(Target)
End If

End Function
Then in use
=myrnd(D3,0.33)
or
=myrnd((SUMPRODUCT(--(C5>={356;396;494;712;1283;1539;3461}),(C5-{355;395;493;711;1282;1538;3460}),{0.19;0.1;-0.08;0.1377;-0.0027;0.045;0.1})),0.33)

where 0.33 or above will be rounded up, 0.329 or below, down

But Narayan's is a good solution
 

Michelle06

New Member
Thank you all for your assistance. These all to appear to do the job. It's great that there is always more than one solution when using excel!
Thanks again it is much appreciated :)
Michelle
 
Top