VBA beginner

[tford75]

Hey, so i have an excel file with tens of thousands of rows of data. The data is sorted by day, and there is about 70 entries per day. so it would look something similar to:

4/15/2005.......36.......72
4/15/2005.......45.......76
4/15/2005.......47.......69
4/15/2005.......52.......59
4/15/2005.......39.......51
4/15/2005.......34.......45
4/16/2005.......39.......60
4/16/2005.......52.......67
4/16/2005.......53.......69
4/16/2005.......58.......76
4/16/2005.......45.......58
4/16/2005.......40.......55

except each day has around 70 entries instead of 6 (but not every day has the same number of entries), and this goes over a period over about 7 years of daily data.

I need to take the Average of collumns B & C, as well as the Max and Min of collumn B. I'm completely new to VBA, trying to learn, and doing this by conventional excel methods will take forever. Any suggestions would be appreciated!
-Thanks

 

[Desu Nota from Columbus]

Sub maxminavg()

Range("D1").Value = "Max"
Range("D2").Formula = "=MAX(B:B)"
Range("E1").Value = "Min"
Range("E2").Formula = "=Min(B:B)"
Range("F1").Value = "Average B"
Range("F2").Formula = "=Average(B:B)"
Range("G1").Value = "Average C"
Range("G2").Formula = "=Average(C:C)"

End Sub

Puts the relevant data in D1:G2 (assumes they are open/unused beforehand)

Does this do what you want?

 

[Ruddles]

Hey, so i have an excel file with tens of thousands of rows of data. The data is sorted by day, and there is about 70 entries per day... I need to take the Average of collumns B & C, as well as the Max and Min of collumn B.

Hey yourself, and welcome to the board.

I'm assuming when you say you want the min/max/average, that you want this on a per-day basis, yes?

If so, I know enough to know that you need a pivot table, a facility which is built in to Excel.

Sadly I'm not an expert on pivot tables - I never use them - but there are probably loads of tutorials on the Web.

Having said that I expect someone to pop up and talk you through it any... moment... now...

 

[tford75]

Yes i need the max/min/average for each day. Sorry i wasnt specific there.

 

[Desu Nota from Columbus]

I didn't realize each day was not on its own sheet. I missed the part that they were in a cumulative table (my fault not yours!).

 

[tford75]

yeah it was just a straight data download with stuff from every day of the past ~7 or 8 years. I'll do some research on pivot tables and see if i can figure out how to work those. Thanks folks.

 

[Desu Nota from Columbus]

I just tested using a pivot table and it worked perfectly.

Highlight all your data and select Data:PivotTable

Select layout during step 3 of 3 of wizard.

I assume your columns are named, Column A= A, Column B= B, Column C=C...

Drag A to the Row Box
Drag B To the Data Box---> double click and set to max
Drag B to the Data Box---> double click and set to min
Drag B to the Data Box---> double click and set to Average
Drag C to the Data Box---> double click and set to Average

And hit OK

 

[Hermanito]

this is indeed a textbook example to use pivottables...

it's not hard to do at all...

I don't have Excel on this pc, so I will write down the steps from memory (for Excel 2003)... you might have to improvise a little bit to get through them

- make sure you have no empty rows in your data
- make sure each column has a unique column header
- make sure the Pivottable toolbar is being shown
- select any cell in the data
- click on the Pivottable item in the Pivottable toolbar to start the wizard
- walk through the steps in the wizard
- most important step is when you get to choose where you want to place the pivottable, choose cell A1 on a new blank sheet
- for the rest accept all defaults
- you should end up now with an empty pivottable on a blank sheet
- make sure the pivottable fields list is being shown (last button on the toolbar)
- drag the date-field into the row-area
- drag the B-field into the data-area
- doubleclick the B-field header in the pivottable, choose average instead of the default sum
- drag the B-field again into the data-area
- doubleclick this new header again, choose min instead of default
- ...
- repeat steps for max B, avg C, min C and max C
- rightclick the date-header in the pivottable
- choose group/ungroup
- by default it will group per day I think
- click OK
- you should have the result you want now...

good luck!

 

[tford75]

thanks guys! sounds much easier!