Rounding formulas

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pdberger - 13 Dec 2007 17:10 GMT

Good morning.

I want the workbook user to be able to round values -- either up to a
selected interval, down, or to the nearest. The only related VBA function
I'm able to find is rounding to a number of digits. For example, I want to
multiply [G8] x [H8], and round to an interval stored in [C4].

Can I accomplish this directly in a VBA statement?

Thanks in advance.

Reply to this Message

Jerry W. Lewis - 13 Dec 2007 17:31 GMT

An example would greatly clarify what you mean by rounding "to an interval"

Jerry

> Good morning.
>
[quoted text clipped - 6 lines]
>
> Thanks in advance.

Reply to this Message

pdberger - 13 Dec 2007 17:44 GMT

Sorry for not being clear enough.

I'd like to multiply the number in [G8] (for example, 11.27) times [H8] (for
example, 71.50), and allow the user to pick an interval stored in [C4] (for
example, 0.25] and round the answer to that -- either the nearest, or forcing
it up or down. There would be many such computations -- we're preparing
price sheets.

So -- result of multiplication is 11.27 x 71.5 = 80.57

I'd like to allow the user to round the result to:

forcing it up (like the Excel RoundUp function) -- 80.75
forcing it down (like the RoundDown function) -- 80.50
nearest (like the MRound function) -- 80.50

Thanks in advance.

> An example would greatly clarify what you mean by rounding "to an interval"
>
[quoted text clipped - 10 lines]
> >
> > Thanks in advance.

Reply to this Message

Bob Phillips - 13 Dec 2007 18:29 GMT

=ROUND(G8*H8,C4)

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Sorry for not being clear enough.
>
[quoted text clipped - 34 lines]
>> >
>> > Thanks in advance.

Reply to this Message

Jim Thomlinson - 13 Dec 2007 17:32 GMT

You can use a simple formula to round to any intervaly you want. Give this a
try.

Dim x As Double

x = Round((Range("G8") * Range("H8")) / Range("C4"), 0) * Range("C4")

Signature

HTH...

Jim Thomlinson

> Good morning.
>
[quoted text clipped - 6 lines]
>
> Thanks in advance.

Reply to this Message

Mike H - 13 Dec 2007 17:41 GMT

an example and clarification of what 'to an interval' means
would have heloped but here are 2 possibles:-

Range("A1").Value = Round((Range("G8").Value * Range("H8").Value),
Range("C4").Value)

Range("A1").Value = WorksheetFunction.RoundDown((Range("G8").Value *
Range("H8").Value), Range("C4").Value)

Mike

> Good morning.
>
[quoted text clipped - 6 lines]
>
> Thanks in advance.

Reply to this Message