I've got an array of values.

When I sum by referencing array entries, I get on thing but if I
sum the values, I get another thing.

It's got to be something around the use of Variant instead of
Double, but I can't figure out what.

e.g.
----------------------------------------------------------------
Return Stream (Date:Rate)
3/1/2010:0.0293,
6/1/2010:0.02674,
9/1/2010:0.0408,
12/1/2010:-0.04

Public Type ReturnInfo
ReturnDate As Variant
ReturnRate As Variant
End Type

Dim myRI() As ReturnInfo

==> (Load stream into myRI and add 1 to each rate) <==

Immediate Window:
----------------
?myRI(0).ReturnRate
1.0293
?myRI(1).ReturnRate
1.02674
?myRI(2).ReturnRate
1.0408
?myRI(3).ReturnRate
0.96

?myRI(0).ReturnRate * myRI(1).ReturnRate * myRI(2).ReturnRate *
myRI(3).ReturnRate
1.05594420486298

?1.0293 * 1.2674 * 1.02674 * 1.0408 * .96
1.33830368524334
----------------------------------------------------------------

Note the diff: 1.055 vs 1.338.

Can anybody explain?

This code was written 7 years ago, and I'm assuming there is a
reason for the use of Variant instead of Double.

If that use explains the problem, I'll change it and start
regression testing.... but a full regression test is going to
take a loooooong time and I want to make sure I understand the
implications first.
--
PeteCresswell

(PeteCresswell) wrote:
IF you changed the type from Variant to Double there is no change. So
that's not your problem. The problem is your transposing of chars.

The line that is giving you fits is the second. it should be
1.0293 * 1.02674 * 1.0408 * 0.96

You had 1.2674, not 1.02674

Per Salad:

Aw geeze...... -)

I should have gone back to Occam's Razor - where the simplest
explanation is RCI.

"Sometimes you kill the dragon... sometimes the dragon kills
you..."

Thanks.
--
PeteCresswell

"(PeteCresswell)" <x@y.Invalid> wrote in
news:lq2tm6hdqdrk8obkescmeffuk8eonjdkdo (AT) 4ax (DOT) com:

Even with no "finger errors", you can't really do what you did as a
comparison, because it exacerbates floating point errors.

The only way to test the output is to take the values returned by
the array, assign each individual one to a variable of an
appropriate fixed data type (not variant) and then use the variables
for the calculation. Then do the same with the literals. Anything
else and you're going to introduce floating point inaccuracies.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

Per David-W-Fenton:
Which begs a question: should one even use Double fields unless
they are absolutely necessary?

Late in the game it dawned on me that, if the user could make do
with no more than 4 decimal places, the "Currency" data type
would be used for something non-currency-related like shares of
stock.

I've got one application where, every so often, a query against
shares that actually net out to zero, will return something like
..000000045644 instead of zero.

Part of me says "bite the bullet and convert". The other part
fears unintended consequences.
--
PeteCresswell

(PeteCresswell) wrote:
If you run this program you will see the diff between Double and Cur.
If you accept the rounding, go Cur. If not, stay as you are.
Sub DblCur()
Dim dbl As Double
Dim intFor As Integer

dbl = 0.00011

For intFor = 1 To 8
dbl = dbl + 0.00001
Debug.Print dbl; CCur(dbl)
Next

End Sub

On Mar 4, 8:36*am, "(PeteCresswell)" <x...@y.Invalid> wrote:

If you're just adding columns or multiplying by a number with up to
the hundredth's place precision, there's no harm in using the Currency
data type (those are the situations that likely caused the gripes that
led to its creation). If you're multiplying by a number with more
than a hundredth's place precision, I recommend using Double or
Decimal for the calculation. I often choose Double over Decimal
because floating point inaccuracies in about the sixth significant
digit (after multiplication) are usually not a concern because if you
can get your final result within a penny most accountants are
thrilled, although some would be mortified at being off by a penny.
For them, use either the Decimal or the Currency type :-). Using
Double to add a column can produce unintuitive results such as someone
checking your sum coming up with a slightly different answer. It's
similar to percentages not adding to 100% when rounding is used.

One thing I don't like about the Currency type is that when you view a
Currency value in a table, such as $5.157, it shows up as $5.16 unless
you click on that line. It gets around a validation aspect I'd like
to have when using a Currency type. Four decimal places is fine for
the calculation, but when I put the result in a table, I'd like to be
prevented from splitting any pennies. However, storing all four
places allows for the 'store two places' possibilities and more (a
feature super-set), so I can't really complain about their design
decision.

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com