Rounding formulas

[pdberger]

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.

 

[Jerry W. Lewis]

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

Jerry

 

[Jim Thomlinson]

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

 

[Mike H]

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

 

[pdberger]

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"

Jerry

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.

 

[Bob Phillips]

=ROUND(G8*H8,C4)

--
---
HTH

Bob


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

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"

Jerry

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.