Rounding up to the nearest .33

[lorber123]

I have a list of numbers that I need to add up. I am using the sum function and then want to round up to the nearest .33 increment. With that said, the last two digits need to be .33, .66, or .99. When I do the sum function, with a round to two digits and then take the right two numbers, it will not always work because if its an even number like .4, it will not show .40 and therefore doing if statements to the right two numbers wont work.

Any suggestions?

[mrice]

How about....

=INT(((ROUND(SUM(A1:A10)*3,0)-0.01)*100)/3)/100

.....assuming the data is in A1:A10

[XOR LX]

"How about....

=INT(((ROUND(SUM(A1:A10)*3,0)-0.01)*100)/3)/100"

What about the rounding up bit?

Maybe:

=SUMPRODUCT(CEILING(MOD(SUM(A1:A10),{1000,1}),{1,0.33}))-1

Regards

[FDibbins]

What about using...
=MROUND(sum(....),1/3)

[XOR LX]

"What about using...
=MROUND(sum(....),1/3)"

I thought about that, but after 0.33, 0.66, 0.99, ... you'll start rounding to 1.32, 1.65, 1.98, etc. and it just gets further and further away...

[FDibbins]

Not from what I had...maybe Im missing something?

	B	C
10	1.5	1.666667

C10=MROUND(B10,1/3)

edit: OK so that will give 1.67, not 1.66 if only 3 dec shown, so then this instead...
=ROUNDDOWN(MROUND(B10,1/3),2)

[XOR LX]

Ah, no. Of course, you're using a 1/3, not 0.33!

Apologies, and very nice! Ok, so now you just have to do something about unwanted rounding, e.g.
what do you get if B10 has, say, 98.93?

Cheers

Edit: In fact, even though it will take a while before using a third will start to lose accuracy, it will happen...e.g. if B10 has 1000.1, the desired answer is 1000.33, yet your formula will give 1000.

[FDibbins]

Hmm using that, I get 99.00 needed to add some stuff...
=IF(MOD(ROUNDDOWN(MROUND(B10,1/3),2),1)=0,ROUNDDOWN(MROUND(B10,1/3),2)-0.01,ROUNDDOWN(MROUND(B10,1/3),2))

[martindwilson]

what about say 2 that rounds to 2 shouldnt it be 1.9999?

[XOR LX]

But now your previously concise, elegant solution is longer than mine!

(And I'm still not sure it work for sufficiently large numbers, e.g. 1000.1 is rounded down to 999.99 in your latest version.)

Regards

[FDibbins]

lol Yes, I know. Thats what happens when you get more complex. Maybe we need more input from the OP

@ martin, thats what my addition was for

[XOR LX]

Had to amend my solution slightly to account for cases where the sum is an integer:

=SUMPRODUCT(CEILING(MOD(SUM(A1:A10)+10^-10,{1000,1}),{1,0.33}))-1

Cheers

[shg]

=int(a1) + lookup(mod(a1, 1), {0,34,67}/100, {33,66,99}/100)

[PietBom]

I extended the formula of shg............

[PietBom]

Extended the formula a little bit more................

[ejfick]

Old thread, but replies helped me get to a similar desired output:

=MROUND((A1+0.16),1/3)

rounds up A1 to 0.00 , 0.33 or 0.67

[FDibbins]

Old thread, but replies helped me get to a similar desired output:

=MROUND((A1+0.16),1/3)

rounds up A1 to 0.00 , 0.33 or 0.67

Thanks for the feedback, Im happy you found some assistance here