Another Internet home for this fine breed of Belgian Shepherd Dog. A place for questions and answers, learning and understanding.
I'm always asking it to do more than it should
Published on May 12, 2012 By LizMarr In Personal Computing

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.

 

.

.

 

 

 


Comments
on May 12, 2012

Add a few columns that parse out the parts of the location you are interested in (yes, using mid&co (hint: instr)).

Then work with those.

 

 

on May 12, 2012

I knew someone would mention OO. Which reminds me, I still haven't installed on this machine yet. I'm doing one program at a time and checking for stability. I don't want to blow it up like I did last time.

I'm dogged determined to figure this out with Excel (the ol' stubborn streak) I haven't even poked around with the OO database, I've mostly been using it to make PDF for the graphics for my books.

on May 12, 2012

http://www.libreoffice.org/

Here's another.

on May 12, 2012

Never heard of that one. Have you tried it?

on May 12, 2012

LizMarr
Never heard of that one. Have you tried it?

It's a splinter of the original OpenOffice code, think they split at around 3.1 or so, IIRC. I've used both and find LibreOffice to be a bit more feature filled, well, at least for the stuff I use it for, so of course your mileage may vary. They are still very similar though at this time.

on May 14, 2012

Leo in WI
It's a splinter of the original OpenOffice code, think they split at around 3.1 or so, IIRC. I've used both and find LibreOffice to be a bit more feature filled, well, at least for the stuff I use it for, so of course your mileage may vary. They are still very similar though at this time.

It splintered because Oracle purchased Sun, who previously had control over OOo.  There was some kerflufle around Oracle being in charge of it, specifically it's future as a truly open source project, so it forked, and LibreOffice was the version that set out to be the more OSS friendly version.

(That's a rough, simplistic history of what happened, anyway)

on May 14, 2012

Some of the devs wanted to take OO and go commercial with it no longer under the GNU license. Other devs told them where to go and developed Libre Office, keeping it under the GNU, open source, license.