• 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


  • 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


New Member

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:


Could anyone please point me in the right direction?



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.


Staff member
finally woke up. Here is another one.


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


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

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

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

End Function
Then in use

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

But Narayan's is a good solution


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 :)