Microsoft Excel Conditional calculation
We are using an excel spread sheet to track the ESN (electronic serial number) on RF id tags as they travel through the reader. I have a column that uses a conditional countif statement to track for duplicate tag numbers and total them.
A flaw in my statement that I can't seem to rectify is this. Not only does it count if there are duplicate tag numbers, it also counts if there are no tag numbers as duplicates. Meaning that the value 0, which is a valid value shows up in the final total and I really would like to filter out the value 0. We do, in another column, total 0 as a "No Tag" count.
Here is my statement as it stands now: =SUM(IF(COUNTIF(CU3:CU4980,CU3:CU4980)>1,1,""))*0.5
I am in no way a math whiz or excel expert, after much trial and error I turn to the experts!
Thank you kindly!
A flaw in my statement that I can't seem to rectify is this. Not only does it count if there are duplicate tag numbers, it also counts if there are no tag numbers as duplicates. Meaning that the value 0, which is a valid value shows up in the final total and I really would like to filter out the value 0. We do, in another column, total 0 as a "No Tag" count.
Here is my statement as it stands now: =SUM(IF(COUNTIF(CU3:CU4980,CU3:CU4980)>1,1,""))*0.5
I am in no way a math whiz or excel expert, after much trial and error I turn to the experts!
Thank you kindly!
If you have Microsoft 365 (and hence the dynamic arrays feature), you get the UNIQUE function. With it, you may count duplicate values with:
=COUNTA(CU3:CU4980)-COUNT(UNIQUE(CU3:CU4980))ASKER
Hi byundt..
Yes we are using O365....
I tried both of your suggestions, the first returned that same value as my original calculation, and the second is 3 units less. 517 instead of 520.
Thank you for the quick response..
Yes we are using O365....
I tried both of your suggestions, the first returned that same value as my original calculation, and the second is 3 units less. 517 instead of 520.
Thank you for the quick response..
Be helpful to see a workbook with a small bit of test data that shows problems with your original formula and the two alternatives. That way I can make sure I am testing with the right kind of data (text that looks like numbers, strictly text, strictly numbers, a mix).
ASKER
Thanks for posting the sample data.
I designed my second formula to count the number of duplicate values, excluding blanks. Your sample data in CU3:CU69 have 20 cells with 0 in them, and will add 19 to the duplicate count. In addition, 584595 and 625640 were each repeated once, so the total number of duplicate cells should be 21--which is what my second formula returned.
My first formula returned 12 because it was dividing the number of cells with a match elsewhere by 2. So the 20 cells with a 0 add 10 to the duplicate count, and the two other values that each had a single duplicate value each added 1. The total of "duplicates" was thus returned as 12.
Modifying the formula to produce the desired results is easy as long as the rules are clear.
I designed my second formula to count the number of duplicate values, excluding blanks. Your sample data in CU3:CU69 have 20 cells with 0 in them, and will add 19 to the duplicate count. In addition, 584595 and 625640 were each repeated once, so the total number of duplicate cells should be 21--which is what my second formula returned.
My first formula returned 12 because it was dividing the number of cells with a match elsewhere by 2. So the 20 cells with a 0 add 10 to the duplicate count, and the two other values that each had a single duplicate value each added 1. The total of "duplicates" was thus returned as 12.
Modifying the formula to produce the desired results is easy as long as the rules are clear.
ASKER
Hmm...
I will try your formulas tomorrow on another full data set...
We generally have around 4500 to 7500 values per data set. I more than likely fat fingered your formulas.
I will let you know how it works.
Thank you kind sir.
I will try your formulas tomorrow on another full data set...
We generally have around 4500 to 7500 values per data set. I more than likely fat fingered your formulas.
I will let you know how it works.
Thank you kind sir.
So are we agreed that the formulas should be counting the number of duplicate values, not including blank cells? In other words, the first instance of a value with duplicates won't be counted, but each additional one will be.
Also, are we agreed that all cells contain either a blank or a numeric value?
Also, are we agreed that all cells contain either a blank or a numeric value?
ASKER
Sorry..I misunderstood your earlier post….
The cells will contain either 0 or a numeric value, there should not be an empty cell. (Although that is a possibility in a data set of 7500 entries or more)
We are trying to locate and count each instance of a duplicate tag number.
In the sample file, cell column A contains a time stamp, we cross reference duplicate tag numbers against the time stamp to validate that the reader has not read a tag twice in the same quarter of a second.
Hope that helps to clear up what we are trying to accomplis.
Thanks!
The cells will contain either 0 or a numeric value, there should not be an empty cell. (Although that is a possibility in a data set of 7500 entries or more)
We are trying to locate and count each instance of a duplicate tag number.
In the sample file, cell column A contains a time stamp, we cross reference duplicate tag numbers against the time stamp to validate that the reader has not read a tag twice in the same quarter of a second.
Hope that helps to clear up what we are trying to accomplis.
Thanks!
So if there are only 10 cells with data (seven containing 0, two containing 1 and one containing 2), the formula should return 7 as the answer. Correct?
ASKER
No, in your example I would expect to see a count of 1. ( 2 cells containing the value of 1 divided in half )
The 7 cells containing 0 should be ignored, the two cells containing the value of 1 would represent 1 instance of a duplicate read. The one cell that contains the value of 2 would not be counted as it did not have a duplicate value in the 10 cell set.
The 7 cells containing 0 should be ignored, the two cells containing the value of 1 would represent 1 instance of a duplicate read. The one cell that contains the value of 2 would not be counted as it did not have a duplicate value in the 10 cell set.
OK. Let's take another example.
Once again with 10 cells: seven with 1, two with 0 and one with 3. Should the answer be 6, 3 or 1?
Once again with 10 cells: seven with 1, two with 0 and one with 3. Should the answer be 6, 3 or 1?
ASKER
The seven cells with the value of 1, should be counted, the two cells with the value of 0 ignored, the one cell with the value of 3, not counted.
The resulting total should be 3.5
7* 0.5 = 3.5 (Seven tags with the ESIN of 1 divided by 2 = 3.5 the total instances of duplicate tag reads of the ESIN 1)
I am wondering if I should just make 3 separate calculations then summarize the results in a different column.
The resulting total should be 3.5
7* 0.5 = 3.5 (Seven tags with the ESIN of 1 divided by 2 = 3.5 the total instances of duplicate tag reads of the ESIN 1)
I am wondering if I should just make 3 separate calculations then summarize the results in a different column.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@byundt
That works perfectly!
I used it against a set of data that contained 27880 rows, your calculation matched my long hand count 100%.
We use that to assure that we stay within a 5% tolerance of reads. In this case we had 0.06%.
Your solution is so much more elegant than my long hand of searching, counting on paper and manually entering that value in the summary report.
Thank You!!
That works perfectly!
I used it against a set of data that contained 27880 rows, your calculation matched my long hand count 100%.
We use that to assure that we stay within a 5% tolerance of reads. In this case we had 0.06%.
Your solution is so much more elegant than my long hand of searching, counting on paper and manually entering that value in the summary report.
Thank You!!
Open in new window