Average

[Darren]

I have written some code to find out average of some
numbers. Although if i used Excel's existing formula,
average, to work it out, the result would be different
(about less than 1% for an average of 720 numbers) from
that derived using my own code.
I wonder if the difference is due to rounding errors. Is
it possible to take a look at the code behind Excel's
existing formulas?

 

[Myrna Larson]

No, it isn't possible. But I suspect the error is on your end <bg>.

 

[Darren]

Thanks for the reply.

Do you know then, the syntax to use if i want to use
Excel's average formula for col A, rows i to j? rows i and
j?
If i and j are both variables

Thanks
Darren

-----Original Message-----
No, it isn't possible. But I suspect the error is on your

 

[Myrna Larson]

You can use most worksheet functions within your VBA code. Exceptions are
cases where VBA has its own function that does the same thing, but that
doesn't apply to AVERAGE. The syntax is

Set Rng = Cells(i, 1).Resize(j - i + 1, 1)
Avg = Application.Average(Rng)

or, putting it all into one statement:

Avg = Application.Average(Cells(i, 1).Resize(j - i + 1, 1))

Thanks for the reply.

Do you know then, the syntax to use if i want to use
Excel's average formula for col A, rows i to j? rows i and
j?
If i and j are both variables

Thanks
Darren

 

[Myrna Larson]

PS: You really ought to study your code to see why you are getting a different
answer. You always learn something from "debugging".

Are you sure this isn't just a rounding issue? If the cells on the worksheet
are the result of formulas, but displayed with, say, 2 decimal places, when
you use the AVERAGE formula, the complete number is used, not the rounded
version that you see. If you are working with the rounded numbers in your
code, you'll get a different result.

Thanks for the reply.

Do you know then, the syntax to use if i want to use
Excel's average formula for col A, rows i to j? rows i and
j?
If i and j are both variables

Thanks
Darren