I am trying to get this formula to work in my macro, but it seems not to
accept the MROUND, I get a Sub or Function not defined error. I need the
MROUND so that I can get proper rounding results. The formula below is
working with times and without MROUND I sometimes get rounding errors. For
example:

15:21 - 15:00 returns .35 which should round to .4 but rounds to .3


TextBox10.Text = Format(Round(MROUND((CDate(TextBox9.Text) _
- CDate(TextBox8.Text)) * 24), 1), "#.0")

Hi Patrick,

When doing calculations in sheets, differecen is shown as 0.35. But when
forcing 15 numbers after comma, it becomes 0.3499999... which is rounded
at 0.3

One (absolutely not nice) method is to first round at 2 numbers after
comma (0.35) and then round at 1 after (0.4)

HTH
@+
FxM



Patrick Simonds a écrit :

I am trying to get this formula to work in my macro, but it seems not to
accept the MROUND, I get a Sub or Function not defined error. I need the
MROUND so that I can get proper rounding results. The formula below is
working with times and without MROUND I sometimes get rounding errors. For
example:

15:21 - 15:00 returns .35 which should round to .4 but rounds to .3


TextBox10.Text = Format(Round(MROUND((CDate(TextBox9.Text) _
- CDate(TextBox8.Text)) * 24), 1), "#.0")

Patrick,

In Excel, go to the Tools menu, choose Add-Ins, and put a check
next to "Analysis Tool Pak - VBA". Then, in VBA, go to the Tools
menu, choose References, and put a check next to ATPVBAEN.xla.
You can then use the functions in the Analysis Tool Pak in your
VBA code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Patrick Simonds" wrote in message
...
I am trying to get this formula to work in my macro, but it
seems not to
accept the MROUND, I get a Sub or Function not defined error.
I need the
MROUND so that I can get proper rounding results. The formula
below is
working with times and without MROUND I sometimes get rounding
errors. For
example:

15:21 - 15:00 returns .35 which should round to .4 but rounds
to .3


TextBox10.Text = Format(Round(MROUND((CDate(TextBox9.Text) _
- CDate(TextBox8.Text)) * 24), 1), "#.0")

mround is in the analysis toolpak.

You need to create a reference to analysis toolpak VBA (ATPVBAEN.xls) then
you can use it like you have shown.

however, the numbers you show produce:

0.349999999999999 (IEEE double precision limitation)

which MRound rounds to .3

maybe just add .001 to the result of multiplication by 24


TextBox10.Text = Format((CDate(TextBox9.Text) _
- CDate(TextBox8.Text)) * 24 + .001)"#.0")

--
Regards,
Tom Ogilvy




"Patrick Simonds" wrote in message
...
I am trying to get this formula to work in my macro, but it seems not to
accept the MROUND, I get a Sub or Function not defined error. I need the
MROUND so that I can get proper rounding results. The formula below is
working with times and without MROUND I sometimes get rounding errors. For
example:

15:21 - 15:00 returns .35 which should round to .4 but rounds to .3


TextBox10.Text = Format(Round(MROUND((CDate(TextBox9.Text) _
- CDate(TextBox8.Text)) * 24), 1), "#.0")