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?
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
TTFN
RE: Data Extraction Question
-pmover
RE: Data Extraction Question
RE: Data Extraction Question
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
Good Luck again,
ProjEngKLS
RE: Data Extraction Question