Rounding and SUMPRODUCT Function

[Michelle06]

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]

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]

Hi ,

What does this do ?

=CEILING.PRECISE(value - 0.333333333333 , 1)

Narayan

 

[r1c1]

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]

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
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]

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