Help formulating totals - max min average on data sheet

[TWillard]

I have an excel sheet that has 52 columns and 4000+ rows. The data in the sheet is raw data. I would like to add three rows at the bottom of my sheet to display max, min, and averages per each column of data. I know that I can click on the function button and manually build a single function. With 52 columns and 3 desired functions per each column that is 156 manually built functions. The data changes each day, so that can compound the work. Does excel have the ability to automate this feature an any way. Can I highlight the data sheet itself and have these totals displayed at the bottom with a few clicks or using an excel feature?

A B C D .....
1 # # # #
2 # # # #
3 # # # #
4
5 max # # # #
6 min # # # #
7 avg # # # #

Thanks in advance for reading this question.

 

[Zathras]

I would write a macro. But assuming that is not what you are after, here is a non-macro approach:

1. Select the cell in the first column where you want one of the formulas.
2. Click the sum tool (Greek capital sigma = [Σ]) twice. That will insert a SUM formula.
3. Edit the formula (F2) and convert the cell references to absolute row / relative column (F4 twice on each end of the range specification) and Change SUM to MAX.
4. Copy the formula down thru the next two rows. (Click and drag the little black square in the bottom right-hand corner of the cell)
5. Change the two new formulas to MIN and AVERAGE.
6. Select all three formula cells and click and drag across your 52 columns.

 

[Gavona]

If the number of rows changes each day it could be much better to put the summaries at the top of the worksheet, above the data. Get them to Sum etc up to row 65536 (the max possible).

If you use Subtotal(9, A3:A65536) you will get the sum but if you filter your data it will only sum the filtered (visible) rows. The "9" in the formula returns sum, 4 will return Min and 5 Max...

Gavin

 

[SkipVought]

it could be much better to put the summaries at the top of the worksheet, above the data

This is VERY GOO advice!

I almost ALWAYS put aggragations at the TOP of a table. WHy make the user HUNT for this IMPORTANT summary data?

Aggregations at the BOTTOM, are a vestage of the paper, pencil and adding machine days. We are waaaaaaaaay past that era!

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[TWillard]

This is great advise by everyone. I just tested out the steps that Zathras provided. I can tell that this is going to increase the productivity, which is exactly what I was looking for.