transform series of 7 numbers in 0-4 scores

[mabras]

Hello,
i am puzzled with this problem...
I have a series of raws with 7 numbers in each raw; sums may vary a lot from raw to raw, and also differences between one number and the other in a same raw.
Now, i need to obtain a much simpler distribution of scores, so reducing differences.
I would need to transform all numbers into a 0-4 score distribution, according to differences between numbers in a same raw. It's like a kind of rank score.


I want to obtain in each raw:
- 1-2 numbers with score = 4 (if three or more numbers have similar values: no 4 scores)
- a maximum of 3-4 numbers with score 2-3 (according to differences between these numbers but also also according to the 4 scores already assigned)
- all other scores = 1 if the numbers are >0
- all other scores = 0 if the numbers are 0


example: |14|23|3|0|45|0|10| => |2|3|1|0|4|0|2|


Would it be possibile to do with Excel?


Thanks a lot!
Mabras

 

[mabras]

Just to try and put it in a simpler manner: I have a distribution of scores (7 scores=columns per N subjects=raws) ranging from 0 to N in each raw, and I would like to transform it into a distribution ranging from 0 to 4, taking into account the differences between scores in a same raw (same subject).
No matter which method... any suggestion?
Thanks!

 

[Snakehips]

mabras,

Can you please post a few more examples of original values and expected scores. Especially, illustrating tied high values.

 

[mabras]

thank you Snakehips!
I imagine that 0-4 scores would be assigned according to distances between original scores; here you have some more examples:

subj01 |0 |23|95|18|04|65|0| => |0|2|4|2|1|3|0|
subj02 |38|15|26|0|148|45|2| => |3|2|2|0|4|3|1|
subj03 |86|64|34|24|62|0 |1| => |4|3|2|2|3|0|1|

thanks again.

 

[Snakehips]

mabras,

I'm sorry if my previous post has raised your hopes of a solution from me.
At first glance I had imagined that you could assign a score of 0 to any 0 results and then use the RANK function in some way to score the remaining values.
However now that I see that you are using some arbitrary 'distances' between values in order to maybe give them the same score, I cannot envisage a solution.

Maybe there is a statistical approach to achieve what you want but if there is then that also is beyond me.

 

[mabras]

thanks anyway!
Yes you are right, I'm not trying to simply rank scores, but to save some information about the differences among scores. What you call "arbitrary distance" should take into account the difference between the higher score and the second, and then between the second and the third, ando so on. Maybe a sort of average difference should be used as a parameter?
Anyone else can help, please?

 

[XOR LX]

Hi,

Could you explain how you arrived at your desired results given in post #4?

Regards

 

[mabras]

Sure XOR, thanks fot your help!
Post #4 is only an imaginary result that i figured out as an example of my desired outcome!
As a principle, original scores should be given the same 0-4 score when their difference is small if compared with the average difference among all scores in a same raw.


The best formula I could think of insofar is:

IF(A1<=0,0;0;IF(A1<=B1;1;SE(A1<=B1*2;2;SE(A1<=B1*3;3;4))))

where A1 is the original score and B1 is the highest value/4


It's not working as i wish expecially in example ss06 (... too many 1 scores!):

ss 01 |0|0|0|0|0|0|1| => |0|0|0|0|0|0|4|
ss 02 |0|1|2|3|4|5|6| => |0|1|2|2|3|4|4|
ss 03 |0|1|2|3|24|25|50| => |0|1|1|1|2|2|4|
ss 04 |2|4|6|45|50|55|100| => |1|1|1|2|2|3|4|
ss 05 |0|20|40|60|80|100|120| => |0|1|2|2|3|4|4|
ss 06 |0|1|2|4|8|16|72| => |0|1|1|1|1|1|4|

... any idea?
mabras

 

[XOR LX]

Ok, thanks, but of course the definition of what consitutes "small" in:

"when their difference is small if compared with the average difference among all scores in a same raw."

would have to be rigorously defined by you in the first instance.

Regards

 

[mabras]

Thanks XOR, you're right, but I am not pretty sure how to define "small if compared".


For example, one method could be: putting all seven scores in descending order, and starting from the highest value, if the difference between the highest and the second highest is minor than the difference between the second and the third, then the first two values should be given the same score, ando so on (with 0 = 0 in any case):
|50|45|25|12|10|6|0| =>|4|4|3|2|2|1|0|


... Does it make sense?


Another way to put it could be: when the difference between two scores in an ordered sequence (highest to lowest) is less than the average difference between all values, they should be given the same score.

thanks!