Excel-comparing datapoints to a criteria (Dcount or Vlookup)

[everest]

I may have asked this question before but I can't seem to find the past post.

I have some water quality data to compare against government standards. Out of a dataset, I need to get a percentage of how many datapoints exceed the standard. Right now, I'm doing this with the Excel function DCount. It gets the job done, but I was wondering if there were any easier way of doing it? Right now, I have to manually "configure" the Dcount function for each pollutant (Arsenic, Lead, Chromium). Problem is, I need to do this for several thousand data points and about two hundred different pollutants.

I have a list of criteria and the dataset.

Here's an example

Dataset

Arsenic 10
Chromium 10
Lead 10
Arsenic 5
Chromium 5
Lead 5
Arsenic 1
Chromium 1
Lead 1
----------------------------------
Criteria
Arsenic <4
Chromium <7
Lead <20
----------------------------------
Desired Output
Arsenic 33%
Chromium 66%
Lead 100%

Basically, the output should show the perctange of datapoints meeting the criteria.

Now, finally getting to my question, is there a way to simplify this process? Ideally, I would like to be able to just highlight the entire criteria table, have it look up the respective pollutant in the dataset and add up the number of datapoints that exceed the criteria. I would guess that the solution involves using a combination of Vlookup and Dcount, but I'm not sure how to go about doing that.

If you think this process would be better suited for Access, please let me know and I&quot;ll go ahead and copy this question there. Thank you for your help.

Everest

 

[SkipVought]

Hi,
If I understand your question, here's how it can be dome with spreadsheet functions...

1. Make a criteria for each element

Element	PPM	
Arsenic	<4	33%
		
Element	PPM	
Chromium <7	67%
		
Element	PPM	
Lead	<20	100%

This was entered in columns I thru K.

2. Use this formula for the percentage...

=DCOUNTA(YourDatabase,"Element",I1:J2)/DCOUNTA(YourDatabase,"Element",I1:I2)

where YourDatabase is a named range referencing your database.

WHen your database expands with more rows, you will need to adjust the range associated with YourDatabase using Menu item Insert/Name/Define -- select YourDatabase and modify the range in the RefersTo: text box.

This can be automated with VBA is you so desire. Check my FAQ or write back.

Hope this helps


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KenWright]

Are all your criteria 'less than's?

If they are then with your data as listed above, but with the criteria limit in Col B as just the number, ie 4 as opposed to <4 you could use the following:-

=SUMPRODUCT(($A$1:$A$1000=A1010)*($B$1:$B$1000<B1010))/COUNTIF($A$1:$A$1000,A1010)

Format the cell as a percentage and copy down. If you have say 50:50 with greater thans and less thans you could always try and group them, then just change the < bit in the formula one time and copy down for the second 50%

Regards
Ken................

 

[KenWright]

Sorry, should have clarified, answer assumes your raw data goes to row 1000, and that your criteria start in row 1010 beneath your data.

Also, am happy to give it a go if you can't get it to work, and wanted to send me the sheet with the raw data on.

Regards
Ken................

 

[everest]

Skip and Ken,

Thanks for your help. I have been running the comparison using Skip's method before I posted the question. I would make a criteria for each pollutant and then modify the previous Dcount formula. It was just getting to be very repetitive manually entering in a criteria for each element. There are about 200 pollutants I need to compare each having maybe 3 or 4 different criteria. So in reality, there are actually 600+ criteria to compare with.

All of my criteria are listed in two columns, one naming the pollutant and the other, concentration. I was hoping I could just use some method to highlight the entire criteria table and have it automated somehow.

I would be interested in learning how to do it in VBA if it isn't too time consuming. In the mean time, I'll just be chugging along as I have before. Thanks again.

Everest

 

[SkipVought]

OK,

Here's how to do it with a Pivot Table.

1. Put all your criteria in one separate table with headings Elements, PPM - I called the table YourCriteria

2. select the entire table (Current Region ToolButton) and name the heading ranges using menu item - Insert/Name/Create.../ check create names in top row.

3. Add a column to YourDatabase - I called it InCriteria with the following formula copied from row 2 down...

=IF(B2<VALUE(RIGHT(INDEX(PPM,MATCH(A2,Element,0),1),LEN(INDEX(PPM,MATCH(A2,Element,0),1)) -1)),1,0)

4. Selecting any cell in YourDatabase, select menu item - Data/Pivot Table
in step 1 - select option for excel list or database and
select the option for Pivot Table
in step 2 - replace the absolute address with YourDatabase
in step 3 - it is your choice of separate or current sheet
click the Layout button
in Layout - drag Element to the Row area
drag InCriteria to the Data area
right click on InCriteria in the Data area
and select Average
finish

VOLA!

Whenever your row of data changes...
1. rename YourDatabase as per above
2. select the PivotTable and select menu item - Data/RefreshData

Hope this helps



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com