Score analysis in groups

Question

Hi, could you help me to perform the analyze the scores in different groups in a better way? The analysis (results in columns F to J) can be done using formulas. But when the number of the goups increased, the analysis is very difficult. Is it possible for a VBA to run the analysis? Thank you.

Columns F to J are the analysis of scores in E column, in specific group.

Answer 1

Pivot tables are great, powerful, and I love using them, but a key thing to remember is that they are not live data, rather data from a moment in time. Anytime you update your data you must refresh the pivot table.

The method presented above is live. Any update in the data immediately updates the value-calculations.

Answer 2

You can do G in PivotTables no problem:

https://www.oreilly.com/library/view/microsoft-excel-cookbook/9781771375481/video235440.html 

.

Do F, Rank / reverse

https://www.youtube.com/watch?v=GuGFxe_cgis 

https://exceljet.net/pivot-table/pivot-table-rank-example 

.

And you can calculate columns for H & I

.

If you provide a small example file, we can show you how PivotTables work. Learning how to generate pivot tables is a very useful long term skill.

.

This sort of summary work is exactly what PivotTables are designed to do.

Actually, you could also use a pivot to create the input table you provided if each unique line combination of DataA, DataB and DataC can be the result of summing (?) more than one input.

.

Upload Example - Share OneDrive File

There is no way to upload example files directly to this forum.

.

Trouble shooting problems using this text only forum can be like a visit to the dentist without anesthetics: a long, slow painful process of us trying to “extract” the clues needed to recognize the problem in a back and forth flow of questions and answers so that we hopefully can eventually come up with a / “the” solution.

.

Often it is faster and easier for everyone if we have a “sample file” get "hands on", to look at, and to “play with”.

.

This next link provides some tips on setting up a sample file and specific instructions for uploading and "sharing" it for us to access:

.

https://answers.microsoft.com/en-us/windows/forum/windows_other-winapps/trouble-shooting-share-onedrive-file/a231a097-bcbf-4e34-ad6c-a33118baf471?tm=1523189328156

.

Includes a link to a macro to randomize text in Word. And another macro to randomize data in Excel

.

Note: make sure to upload to your Personal (consumer) OneDrive rather than your work OneDrive or SharePoint. Files in the Work cloud can be hard to share due to security restrictions placed by the work admins. Or use any other free storage service (anything other than business OneDrive or SharePoint).

.

2021 05 25- 15 Best Free Cloud Storage in 2021 – Up to 200 GB Free Storage
https://www.whizlabs.com/blog/best-free-cloud-storage/

.

Answer 3

Paste this formula in F2; then Fill Down:

=COUNTIFS(B:B,B2,E:E,">"&E2)+1

Paste this formula in G2; then Fill Down:

=AVERAGEIF(B:B,B2,E:E)

Paste this formula in H2; then Fill Down:

=E2>G2

Paste this formula in I2; then Fill Down:

=E2<G2

Enter this formula in J2; then Fill Down:

=COUNTIFS(B:B,B2,E:E,"<"&E2)+1

PS: The group2 values below rank and average different from yours because I do not know what the values are for rows beyond No. 17.

Answer 4

Hi，EricCollins5,

Thank you so much for your respons to my post. I have tried your formulas to do the score analysis, they are very good doing the job.

The group2 average in my old table was not correct when some of the data were deleted(not shown). Thank you for letting me know my mistakes.

I am grateful for your help.

Answer 5

Hi，rohnski2，thank you so much for your respons to my post.

I have tried pivot table to do the analysis of the scores. Yes, it solved the problems, though several steps need to be done. You are absolutely right about Pivot tables is a very useful long term skill, I will use it in my work. I also thank you for letting me know a way to upload example files for sharing. I am grateful for your help.