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
Description	My Data
Maximum
Minimum
Average
Medium
Mode
Amt of numbers
Amt of positive numbers
Amt of negative numbers
Amt of numbers = to 77

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Results[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]99[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]207[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]111[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]352[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-202[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]77[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-101[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]77[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]491[/TD]

</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
Description	My Data
Maximum
Minimum
Average
Medium
Mode
Amt of numbers
Amt of positive numbers
Amt of negative numbers
Amt of numbers = to 77

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Results[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]491[/TD]
[TD="align: right"][/TD]
[TD="align: right"]99[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]-202[/TD]
[TD="align: right"][/TD]
[TD="align: right"]207[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]123.4444[/TD]
[TD="align: right"][/TD]
[TD="align: right"]111[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]99[/TD]
[TD="align: right"][/TD]
[TD="align: right"]352[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]77[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-202[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]77[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]1414[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-101[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]-303[/TD]
[TD="align: right"][/TD]
[TD="align: right"]77[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]491[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=MAX(D:D)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B3[/TH]
[TD="align: left"]=MIN(D:D)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]=AVERAGE(D:D)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B5[/TH]
[TD="align: left"]=MEDIAN(D:D)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B6[/TH]
[TD="align: left"]=MODE(D:D)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B7[/TH]
[TD="align: left"]=COUNT(D:D)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B10[/TH]
[TD="align: left"]=COUNTIF(D:D, 77)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B8[/TH]
[TD="align: left"]{=SUM(IF(D:D>0,D:D))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B9[/TH]
[TD="align: left"]{=SUM(IF(D:D<0,D:D))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[GabyB]

Thanks!