PDA

View Full Version : Problems in calculation of Means, Max, Min in VBA

Ann_BBO
07-23-2007, 11:53 PM
I have the question about calculation of Means, Max, Min in VBA. Since, I do not recommend to do it in excel as the time is varied. Sometime maybe 5 time, sometime maybe 30 times etc. The time depends on the Listcount. Therefore, I try to do the below VBA command to get the value.

Means

Dim a As Integer
Dim n As Integer
For x = 0 To ListBox1.ListCount ? 1
y = x * 38
.
.
a = Cells((y + 39), 5).Value
n = n + a
Next x
Range("B3").Value = Application.Sum(n) / ListBox1.ListCount

Max, Min (Use Max be example)

Dim x as Integer
Dim b as Integer
For x = 0 To ListBox1.ListCount ? 1
.
.
If b < Cells((y + 39), 5).Value then
b=Cells((y + 39), 5).Value
End if
Next x
Range("B4").Value = b

The concept like this, but it will show the error when run VBA.
Thanks

xld
07-24-2007, 12:29 AM
Since, I do not recommend to do it in excel as the time is varied. Sometime maybe 5 time, sometime maybe 30 times etc. ....

What does that mean?

What error are you getting and where?

Ann_BBO
07-24-2007, 01:05 AM
Ok
I means that if i use the worksheet formula to calculate the means, max min , then i know that it will be difficult to do that as the time is different. For example, 3 times means =sum(F39+F49+F59)/3, 30 times means=sum(F39+F49+F59+F69+...)/30.
Therefore, using VBA to calculate may be possible.

The error is "type mismatch".
Sorry for any inconvienced caused

xld
07-24-2007, 01:13 AM
Is the data sparsely populated throughout F, that is just blanks apart from a few cells, or are you trying to pick out certain items (if so, what is the criteria). Formulae shgould be able to handle it.

Ann_BBO
07-24-2007, 04:30 AM
I don't know is it possibe for this way. Not using VBA to do it, Only use excel formula to calculate it.

Now, my worksheet range ("F6") will have the number after loading VBA and range ("B3") to find the max. number.

If F6=1, then B3=MAX(F39) "Must Start from F39"
If F6=2, then B3=MAX(F39, F77) "The range difference must be 38"
If F6=3, then B3=MAX(F39, F77, F115)
If F6=4, then B3=MAX(F39, F77, F115, F153)
.
.
If F6=n, then B3=MAX(F39, F(2*38+1), F(3*38+1), F(4*38+1)...F(n*38+1))

Although i find the sequence, my maths is very bad and i don't know how to write this excel formula.(Or maybe impossible)
Thanks

xld
07-24-2007, 06:42 AM
This should do it

=MAX(N(OFFSET(\$F\$9:F999,(ROW(INDIRECT("1:"&F6))-1)*8,0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.When editing the formula, it must again be array-entered.

Ann_BBO
07-24-2007, 07:02 AM
Thanks Xld
Would you mind to explain the above formula. It is because i need to calculate the means, min. standard deviation in my street.
Also, for my above case, the formula is it:
=MAX(N(OFFSET(\$F\$39:F999,(ROW(INDIRECT("1:"&F6))-1)*8,0)))

Thanks again

xld
07-24-2007, 07:43 AM
All you need to do is replace MAX with whichever function you want to use, the rest is generic.

One small thing, I tested on gaps of starting at F9, so your formula should be

=MAX(N(OFFSET(\$F\$39:F999,(ROW(INDIRECT("1:"&\$F\$6))-1)*38,0)))

and change F999 to some point you will never use.

Ann_BBO
07-25-2007, 04:28 AM
I want to know the "N" meanings of this equation.
=MAX(N(OFFSET(\$F\$39:F999,(ROW(INDIRECT("1:"&\$F\$6))-1)*38,0)))

Also, INDIRECT("1:"&\$F\$6) means if F6=4, then it will count from 1 to 4. Moreover, Is it to press Ctrl-Shift-Enter on the all excel formula, all formula will be the array type. And i want to know that is it press Ctrl-Shift-Enter, then the above formula can learn to get the data between 38 range difference.

Thanks again to u !!

xld
07-25-2007, 04:41 AM
The N is an Excel function. It is used here because OFFSET returns an array of references to cells, so we use N to convert it to an array of values, which MAX works upon.

It is ROW(INDIRECT("1:"&\$F\$6)) that does the work. This creates an array of 1:n where n is the value in F6. This is multipled by 38 to get an array of cells offset from F39, the highr that F6 is, the bigger the array of offsets. It is this array of offset values that OFFSET uses to work out range referneces to the cells that we are interested in.

This formula has to be confirmed with Ctrl-Shift-Enter because it is working with arrays of data. If you don't array enter it, it will ALWAYS take the first value, whether that is the MAX or not. Array entering a formula such as =SUM(A1:A10) is pointless and inefficient.