Excel relative question?

Question

I am trying to make a macro that will give me the minimum, average, and maximum of approximately 10 columns that are 1000+ lines long. What I want to do is select the cell 3 lines below the last row of numbers and have it get the min,avg,max of the data above it.

The problem is with each different excel sheet I have to do, the number of rows can change dramatically. Instead of having a fixed "size" selection where the macro reads "select the row 3 lines up from it and this specific number above it". Is there anyway I can make a macro that can read moreso like "select the row 3 lines up from it and and continue to C3"? so that way the number of rows can vary.

Thanks

Answer 1

The macro below starts by going to B5 (row 5 col 2), which it assumes is within your first column of data:



Sub MinAvgMax2()

Application.Goto Reference:="R5C2"

Selection.End(xlDown).Select

ActiveCell.Offset(2, -1).Range("A1").Select

ActiveCell.FormulaR1C1 = "Min"

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = "Avg"

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = "Max"

ActiveCell.Offset(-2, 1).Range("A1").Select

ActiveCell.FormulaR1C1 = "=MIN(R[-8]C:R[-2]C)"

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = "=AVERAGE(R[-9]C:R[-3]C)"

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = "=MAX(R[-10]C:R[-4]C)"

ActiveCell.Offset(-2, 0).Range("A1:A3").Select

Selection.Copy

ActiveCell.Offset(0, 1).Range("A1:I1").Select

ActiveSheet.Paste

End Sub

Answer 2

The easiest way would be to use absolute referencing, then copy the cells.



My first sheet has 1000 columns A3:A1002, so I enter the following formulas in cells A1005:A1007:



=MIN(A$3:A1002)

=AVERAGE(A$3:A1002)

=MAX(A$3:A1002)



The $ before the the 3 tells it to remain 3 even if you copy the formulas up or down. You can then copy cells A1005:A1007 and paste them three cells below any length column, and it will give you these statisics from the row three lines up too row 3.

Answer 3

Why not put the Average, Max, and Min at the top? You don't need a macro either:

=AVERAGE(A5:A65536)

=MAX(A5:A65536)

=MIN(A5:A65536)



Even better, put them on a separate sheet:

=AVERAGE(Sheet1!A:A)

=MAX(Sheet1!A:A)

=MIN(Sheet1!A:A)