Ways to find the variation in a set of values

[Akarty]

Hi,

I have a table with the results from a test done on a product by 6 QAs. I am looking to calculate the variation in the results for each criteria.

For ex: Criteria 1 has 3 entries as "1"/"TRUE" and I want to know the variation from the whole 6 entries for a given criteria.

Pleas enote that I don't have the actual value for the criteria. Atatched sample workbook

Thanks

[MrShorty]

You are probably going to need to explain to us how you want to calculate variation in these data. We are pretty good at Excel programming and, once we understand the computation algorithm can usually come up with a sequence of operations/functions that will replicate that algorithm. But we usually don't have the necessary expertise in the project behind your question to reverse engineer the algorithm you need. Help us understand how you want to calculate the variation in these boolean values, and we should be able to help you program something into the spreadsheet.

[Akarty]

Thanks MrShorty!

These results will be used to identify the alignment amongst the QAs performing the testing. The actual testing comprises of multiple products and we need to identify the product that has highest variation in values amongst the 6 QAs who are perfrming the activity so as to narrow down on any knowledge gaps , missing resources to identify defects and thereby work towards enhancing our resources that aid QAs to perform these types of testing.

So for instance, we take Criteria 1 - there are three 0s and three 1s. I want to know how many times there is a varying result from one another and for some criteria all 6 QAs may have the same value which will give me an agreement of 100%.. Will "VARA" be useful to compute the variation? In other words, I want to have the percentage of agreement for each criteria for a given product.

[MrShorty]

VARA() is a specific statistical function that calculates the variance (as statisticians understand) of a dataset about the mean. I am not enough of a statistician to say for certain that the variance function is not applicable to your problem, but you don't seem to be talking about variance or standard deviation about a mean, so I would say that VARA() is probably not the function you want. However, you are the expert on the statistics of your problem and may know differently.

for some criteria all 6 QAs may have the same value which will give me an agreement of 100%.. Will "VARA" be useful to compute the variation? In other words, I want to have the percentage of agreement for each criteria for a given product.

That sounds to me more like a COUNTIFS()/COUNT() or maybe SUM()/COUNT() function. =COUNTIFS(H2:H7,1)/COUNT(H2:H7) or =SUM(H2:H7)/COUNT(H2:H7). The numerator returns how many 1 entries there are in the range, and the denominator returns how many numeric entries there are in the range. The ratio of those two (formatted as percentage) is the percentage of the range that contains 1.

[Akarty]

Hi MrShorty!!

The countif formula solved my issue. May thanks

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.