Before anyone spouts off with, "Excel isn't a spread sheet" I know that. If I could afford it I would get Access. I'm remotely familiar with Access having created a couple of commercial apps in the far past. But, alas, I have Excel. I taught and acted as a instructor aide, also in the far past, so I can do just about anything normal (did I say NORMAL?) with Excel. If I can't do them, then I know it can be done and research them. We're talking plan ol' office type stuff, nothing fancy.
I'm a closet dataholic. I love to crunch numbers, rearrange them, stare at them, then wait until the next time to do it again. Each time I learn a little more about Excel and number crunching. I never messed with array before - why? At the time I had access to Access and didn't need array in Excel. Now I'm being pulled kicking and screaming into the world of arrays (do I know what is happening? No) I love the new Office (new to me - Office 2007) I'm finally getting used to the "Ribbon" and I love colors so the Conditional Formatting produces a true high. If we had this back in '90 when I was teaching things would have gone a lot faster (remember, these were office workers and nurses and such not programmers) If you are looking for a ready supply of data that can be as complicated as you want then get either the weather data or the earthquake data. It makes a dataholic's heart purr with pleasure. That is until they ask the stupid program to do something with the data that it can't figure out. So here is what I'm appealing to you folks.
Here is a snippet of the data:
| Magnitude |
Location |
Latitude |
Longitude |
Depth (km) |
Date (UTC) |
| 5.3 |
135km ne of bitung, indonesia |
2.2303 |
126.1192 |
55.57 |
5/12/12 13:23 |
| 4.3 |
134km nne of cold bay, alaska |
56.3215 |
-161.975 |
194.57 |
5/12/12 5:39 |
| 4.9 |
southern xinjiang, china |
38.3112 |
89.3698 |
13.86 |
5/12/12 1:34 |
| 4.4 |
168km w of port hardy, canada |
50.6421 |
-129.807 |
9.87 |
5/12/12 0:58 |
| 4.4 |
224km wsw of sinabang, indonesia |
1.9177 |
94.4382 |
9.98 |
5/12/12 0:08 |
| 4.3 |
26km ssw of champerico, guatemala |
14.0699 |
-91.9923 |
59.65 |
5/11/12 23:37 |
| 4.5 |
37km sse of lanta timur, indonesia |
-8.9464 |
119.083 |
122.98 |
5/11/12 23:05 |
| 4.7 |
51km w of riverton, new zealand |
-46.2714 |
167.363 |
10.13 |
5/11/12 22:07 |
| 4.3 |
86km s of unalaska, alaska |
53.0994 |
-166.607 |
32.3 |
5/11/12 21:08 |
| 5.5 |
287km ssw of hachijo-jima, japan |
30.6524 |
138.749 |
384.13 |
5/11/12 20:34 |
| 4.4 |
56km se of embarcacion, argentina |
-23.5341 |
-63.6667 |
543.77 |
5/11/12 19:26 |
| 5.5 |
76km s of ayia napa, cyprus |
34.3026 |
34.1576 |
19.75 |
5/11/12 18:48 |
| 5 |
291km ene of kuril'sk, russia |
45.73 |
151.5414 |
48.36 |
5/11/12 18:37 |
| 4.5 |
34km ssw of kitakata, japan |
37.3627 |
139.7237 |
111.35 |
5/11/12 17:59 |
| 5.3 |
23km n of hojai, india |
26.2124 |
92.8728 |
11.45 |
5/11/12 12:41 |
| 5 |
85km s of jinchang, china |
37.7353 |
102.0476 |
31 |
5/11/12 10:18 |
.
I have extracted by hand a simple form of the list of Locations:
| Region |
Greatest |
Number |
| indonesia |
0 |
93 |
| japan |
|
76 |
| india |
|
74 |
| sumatra |
|
66 |
| indian |
|
53 |
| mexico |
|
51 |
| chile |
|
23 |
| papua |
|
23 |
| turkey |
|
21 |
| fiji |
|
20 |
| russia |
|
19 |
.
I managed to extract the number of events in each area using countif with wildcards and my extrapolated list: =COUNTIF($B:$B,"*"&J2&"*")
Now, for the thing I am working on. How do I search and extract the max size earthquake from the data? Because the Location varies - like there might be five locations in Japan - I need to use my list to group the data, then I have a formula that I can use. The problem with the formula is that you cannot use wildcards so it won't work the same way that the count of events did. Here is the formula that will work on a one-word Location (prior format that the USGS used had some one word Locations) {=MAX(IF(N2:N1308="VANUATU",$F$2:$F$1308))} Notice this is an array (and from another sheet) This gives me the biggest earthquake for Vanuatu (which I would never live at, they have earthquakes constantly - and big ones too) What I want is a formula, none of these left,right,mid,left,mid,mid type of ones. But one with simple logic like these have. I rather think an array would work, only my knowledge of arrays is limited and the stuff on the net written by talking head geeks is nearly impossible to understand.
Now, if we can find a way to identify Papua New Guinea and PNG as the same thing that would be fine as well. The goal is to just drop the data into the spreadsheet without editing it and have it work.
.
.