Functions in VBA

[GabyB]

Hi I was wondering can anyone help me with this problem, I need a way to calculate the max, min, average, median, mode, amount of numbers, amount of positive numbers, amount of negative numbers, amount of numbers that are equal to cero, the sum of all the positive numbers, the sum of all negative numbers, and the sum of all numbers, the problem is that The column where all the data can be found changes and doesn't have an specific range it is just all the numbers found in Column C, also the data is on Worksheet "Table" and the max, min, average etc. must be display on worksheet "Summary" can anyone help me please? it's urgent

 

[jim may]

Starting with this sample Worksheet showing...

Excel 2010

	A	B	C	D
1	Description	Results		My Data
2	Maximum			99
3	Minimum			207
4	Average			111
5	Medium			352
6	Mode			-202
7	Amt of numbers			77
8	Amt of positive numbers			-101
9	Amt of negative numbers			77
10	Amt of numbers = to 77			491

<tbody>
</tbody>

Sheet1​

Then - Run this Macro:

Sub Foo()
Cells(2, 2).Formula = "=Max(D:D)"
Cells(3, 2).Formula = "=Min(D:D)"
Cells(4, 2).Formula = "=Average(D:D)"
Cells(5, 2).Formula = "=Median(D:D)"
Cells(6, 2).Formula = "=Mode(D:D)"
Cells(7, 2).Formula = "=Count(D:D)"
Cells(8, 2).FormulaArray = "=Sum(IF(D2:D10>0,D2:D10))"
Cells(9, 2).FormulaArray = "=Sum(IF(D2:D10<0,D2:D10))"
Cells(10, 2).Formula = "=Countif(D:D, 77)"
End Sub

Then (Afterwards) your worksheet should look this away...

Excel 2010

	A	B	C	D
1	Description	Results		My Data
2	Maximum	491		99
3	Minimum	-202		207
4	Average	123.4444		111
5	Medium	99		352
6	Mode	77		-202
7	Amt of numbers	9		77
8	Amt of positive numbers	1414		-101
9	Amt of negative numbers	-303		77
10	Amt of numbers = to 77	2		491

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B2 =MAX(D:D) B3 =MIN(D:D) B4 =AVERAGE(D:D) B5 =MEDIAN(D:D) B6 =MODE(D:D) B7 =COUNT(D:D) B10 =COUNTIF(D:D, 77) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula B8 {=SUM(IF(D:D>0,D:D))} B9 {=SUM(IF(D:D<0,D:D))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[GabyB]

Thanks!