Functions in VBA

GabyB

New Member
Joined
Mar 19, 2016
Messages
6
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,478
Starting with this sample Worksheet showing...

Excel 2010
ABCD
1DescriptionResultsMy Data
2Maximum99
3Minimum207
4Average111
5Medium352
6Mode-202
7Amt of numbers77
8Amt of positive numbers-101
9Amt of negative numbers77
10Amt of numbers = to 77491

<tbody>
</tbody>
Sheet1



Then - Run this Macro:

Code:
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
ABCD
1DescriptionResultsMy Data
2Maximum49199
3Minimum-202207
4Average123.4444111
5Medium99352
6Mode77-202
7Amt of numbers977
8Amt of positive numbers1414-101
9Amt of negative numbers-30377
10Amt of numbers = to 772491

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
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
CellFormula
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>
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,329
Members
414,055
Latest member
mcarduner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top