Problem comparing differing numerical ranges

Hello

Thanks for your time in reading this.

I have two separate data sets of marks that have been processed via differing assessment schemes and are therefore of different numerical ranges to one anothe; one in the nineties on average and another in the seventies on average.

What I am trying to do is devise a manner by which Group 1 can be analysed and in turn find the respective comparitive mark between both ranges. So there is a comparitive mark input into Column C relating to Column A/Group 1 which is comparible with Column B/Group 2.

For example, if a cell in Group 1 has an average of 97 and Group 2 an average of 76 the comparitive average from Group 1 to 2 would be 75 for example.

Hope that makes sense. Data sheet attached if that helps.

Many Thanks

Please explain your logic and, in the workbook, add 10-15 rows of MANUALLY CALCULATED results to show what you want. Thanks.

HOW do you get 75???

Why are you talking about transposition in your thread title? I can't see how this has anything to do with transposing data. Sorry, confused!!!

Sorry - I still don't understand what you are trying to do, or why you have multiplied one number by 0.79.

Are you able please to articulate the purpose of all this? Are you perhaps trying to SCALE a range of marks? Marks I get (I am a retired teacher), but what you are trying to do with these is still (I am sorry to admit) eluding me.

I think you are going to need to give more: what are the two mark ranges out of? Group one out of 100? Group 2 out of ???

I see. Right, let me have a quick think.

No. I have absolutely no idea what you are trying to do. what should your expected result look like??? a number, 10 numbers? a range???

I am no mathemetician (I taught MFL!), but I think this will calculate the equivalent average for the second group:

=SUMPRODUCT(B2:B22*MAX(A2:A22)/MAX(B2:B22))/COUNT(B2:B22)

This is based on a scaling of Group 2 marks to match the range of marks in Group 1.

AliGW on MS365 Insider (Windows) 64 bit

	F	G
1	Comparitive Mark Output
2	Group 1 Average	Group 2 Average
3	97.32	76.87
4	95.56

Sheet: Sheet1

If it is accurate (need a maths wizard to check my logic), it seems to suggest that the scaled average for Group 2 is around 2% lower than Group 1.

This may all be horribly flawed, though ...

Is there any TEXT anywhere in the range in A2:A59??

This bit:
=SUMPRODUCT(A2:A59*MAX(B2:B754)/MAX(A2:A59))/COUNT(A2:A59) will fall over if there is text (or a number that looks like text) present in the column A cells

If you want to do it the other way around, you need to change it from this:

=SUMPRODUCT(A2:A59*MAX(B2:B754)/MAX(A2:A59))/COUNT(A2:A59)

to this:

=SUMPRODUCT(B2:B59*MAX(A2:A754)/MAX(B2:B59))/COUNT(B2:A59)

I think.

Please do mark the thread as solved once you are happy.

So in each case, to scale a score, we are doing the following:

1. Multiply the scores for the group (e.g. Group 1) by the MAX score in the other group (e.g. Group 2).
2. Divide this by the MAX score for the group (e.g. Group 1). This has now scaled the scores.
3. Divide the result by the number of pupils in the group (e.g. Group 1) to find the scaled average.

If you want to scale the other way, simply invert everything (e.g. Group 1 becomes Group 2 and vice versa).

Generally speaking, if you want to convert any score, you follow this process. For example, if I want to take a score out of 10 and scale it to a score out of 15, I do the following:

Please Login or Register  to view this content.

So you multiply by the score you want it to be out of and divide by the score it was out of originally.