×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Data Extraction Question

Data Extraction Question

Data Extraction Question

(OP)
I have an Excel spreadsheet that contains soil data from over 4,000 sites over a four year period.  Data contains columns for "Site Number", "Location", "Year", "Month", "Depth", "Moisture", etc.  I need to extract the moisture data for a given month, location(s) and depth range, average the results, and then plot them on a graph of "average moisture vs time."

Can this be done in Excel, or does it need to be done in Visual Basic?  Any sample code that you could share?  Guidance?

RE: Data Extraction Question

Can you turn on Autofilter and select what you need?

TTFN

RE: Data Extraction Question

In addition to IRstuff (AutoFilter is a great tool!), use xl's help system to learn how to do the averaging and prepare the chart.  i believe you "might" need to use the advanced filter capabilities to accomplish desired results.  Again, use the Help system.
-pmover

RE: Data Extraction Question

Also look at the "group and outline" functionality.  You might be able to pre-sort you data into groups, and simply chart all of the data.  Then, by carefully placing your "group"'s, you can zip up, or not show the data that you are not concerned about.  The chart's you create will only be based on the data that is visible.  The nice thing about this approach is that you can easily create charts on different pieces of data, while the formatting / scale / size / etc. all stay constant.

RE: Data Extraction Question

Set up an array formula.

Suppose "Site Number" is in range A5:A4005, "Location" is in range B5:B4005, "Year" is in range C5:C4005, "Month" is in range D5:D4005, "Depth" is in range E5:E4005, "Moisture" is in range F5:F4005, etc.

Suppose your criterias "Month" is in range D4, "Location" is in B4, "Depth" is in range E4 (you're after a range, so you'll actually have a MinDepth (say in cell E3) and MaxDepth (say in cell E4) for criterias).

You can count how many entries meet the criterias "Location", "Month", and "Depth" with the array formula:

=SUM(IF(B5:B4005=B4,IF(D5:D4005=D4,IF(E5:E4005=E4,1,0))))

You can sum the moistures meeting the three criteria with the array formula:

=SUM(IF(B5:B4005=B4,IF(D5:D4005=D4,IF(E5:E4005=E4,F5:F4005))))

TO ENTER AN ARRAY FORMULA you type the formula into the edit field and then enter it with the combination keystrokes CTRL+SHIFT+ENTER.

Of course the average is just the Sum divided by the Count.  I don't know if you can combine these into a single formula.  Maybe worth a try...
 
I noticed you want this to work with a range of depth.  I didn't work that part completely out, but you should be able to do this by testing the Depth data against the Depth Criteria.  Maybe something like replace "IF(E5:E4005=E4" with "IF(E5:E4005>E3,IF(E5:E4005<E4" will work.  You'll have to play with this part a bit.

Good Luck,

ProjEngKLS

RE: Data Extraction Question

Oh, and using Autofilters and Subtotals should work too.  Refer to Excel help reference on subtotals - you can use these to do several things like sum, count, etc and they ignore data that is hidden or filtered out.

Good Luck again,
 

ProjEngKLS

RE: Data Extraction Question

Another option may be to use the pivot table option, this will allow you to consolidate the data in categories and will automate the charting as well.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources