# 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

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.

#### NARAYANK991

##### Excel Ninja
Hi ,

What does this do ?

=CEILING.PRECISE(value - 0.333333333333 , 1)

Narayan

#### r1c1

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