Max Values of Multiple Ranges

cubfan83

New Member
Joined
Jul 31, 2007
Messages
2
Good morning to all! This is my first post (I've viewed several) on this forum, so please be patient :)

Here's what I'm looking to do:

I have a spreadsheet with hundreds (about 800) different ranges, and I need to find the max value for each range. Each range consists of 1 city, with 3 columns after each city that has 3 different data points. It just so happens that if I have 15 rows for a city, then there might be 12 different data points in each column for each time my city is listed.

Yes, I could easily copy and paste the MAX function to find the MAX for each range, but with over 800 different ranges, that could take awhile and writing some macros would make it A LOT faster.

I have 2 blank rows after each range, and I'm looking to insert that MAX in the first blank row under each. Then there is another blank row before the new city's range. Hopefully this makes some sort of sense. Any help that anybody can provide would be excellent!!
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
876
The way your data is set out is making the solution awkward. A cleaner way of setting out the data would be to have one very long list rather than 800 different data ranges. Column A would contain the name of the city and column B would contain a datapoint for that city.

On a second worksheet you would then run a pivot table off of the initial dataset. The pivot table would list each city and the maximum of all of the data points for that city. It would be much easier to navigate than scrolling through numerous ranges and finding a summary value at the bottom of each.

It really helps to separate the base data and the presentation of that data.

You may need further help to build the pivot table but they are are really helpful to know about and use.
 

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
876
Here's an example of what what I mean by the base data set:

City Temperature
London 22
London 24
New York 28
Edinburgh 19
Toronto 25
London 21
etc.

The list doesn't need to be sorted by city. The pivot table will take care of that. You may need some procedure to ensure that you haven't entered the same data more than once.
 

cubfan83

New Member
Joined
Jul 31, 2007
Messages
2
I have actually used that method, but there's more information on the original spreadsheet that would be nice to keep in place. I can play around some more with the pivot table to get it to lay out the way that I need.

If anybody else has any other suggestions I'll listen :)
 

Forum statistics

Threads
1,181,367
Messages
5,929,552
Members
436,677
Latest member
CathalP1992

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