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"ll go ahead and copy this question there. Thank you for your help.
Everest
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"ll go ahead and copy this question there. Thank you for your help.
Everest