Scoring a number based on rules

[JonathanWootton]

Hi all,

I am struggling with an excel task, that i suspect could be simple.

I have a column of numbers: eg

7763
7777
8822
1234

And i need to score the numbers based on some simple rules, being
if second number +1 of first number add 1, and second number -1 of first number add 1, if second number = first number +2 and so one.

so:

7763 would score 3
7777 would score 6
8822 would score 4
1234 would score 3

How can i do this easily (I am guessing VB might help, but struggling to get my head into it working)

Any pointers appreciated
Regards
Jonathan

 

[Gerald Higgins]

Hi Jonathan, welcome to the board.

I don't fully understand how your logic works.

For 7763, are you saying it scores 3 because
a) 1st digit = 2nd digit - 2 points
b) 2nd digit is 1 more than 3rd digit - 1 point
c) 3rd digit is not within 1 point of 4th digit, therefore 0 points, TOTAL 3 POINTS ?

 

[JonathanWootton]

Hi Gerald,

Yes thats what i was trying to get at, sorry my explanation probably wasnt great.

I think I need to use Length of number and run through iteratively scoring, but I am not sure.

 

[Gerald Higgins]

This isn't very elegant, but it does it for the first pair of digits.

You should be able to adapt this for the other two pairs.

=(LEFT(A1,1)=MID(A1,2,1))+AND((LEFT(A1,1)+0)>MID(A1,2,1)-1,
(LEFT(A1,1)+0)<MID(A1,2,1)+1)

It feels like there should be a neater formula solution than this, but I can't think of it at the moment

 

[JonathanWootton]

No thats fine, I am after a quick fix on it - and was drawing a blank, thanks for the help

BR,
Jonathan

 

[GlennUK]

An array formula ( entered using Ctrl-Shift-Enter, instead of Enter ) of:

=SUM(IF(ABS(MID(A1,ROW($2:$4),1)-MID(A1,ROW($A$1:$A$3),1))=0,2,0))+SUM(IF(ABS(MID(A1,ROW($2:$4),1)-MID(A1,ROW($A$1:$A$3),1))=1,1,0))

should do it.

 

[Desu Nota from Columbus]

Glenn,

Can you tell me why this doesn't return a 1 for 7763?

=IF((MID(A1,3,1))=(MID(A1,2,1)+1),1,IF((MID(A1,3,1))=(MID(A1,2,1)-1),1,IF((MID(A1,3,1))=(MID(A1,2,1)),2,0))))


And Glenn's formula does the trick!!

 

[Desu Nota from Columbus]

I think I might have just answered my own question:


I added the following changes:

=IF((--MID(A1,3,1))=(--MID(A1,2,1)+1),1,IF((--MID(A1,3,1))=(--MID(A1,2,1)-1),1,IF((--MID(A1,3,1))=(--MID(A1,2,1)),2,0))))


Does this mean that the left, mid, and right functions return text, even if they are searching a number?

 

[GlennUK]

Glenn,

Can you tell me why this doesn't return a 1 for 7763?

=IF((MID(A1,3,1))=(MID(A1,2,1)+1),1,IF((MID(A1,3,1))=(MID(A1,2,1)-1),1,IF((MID(A1,3,1))=(MID(A1,2,1)),2,0))))


And Glenn's formula does the trick!!

Because you are comparing text ( MID ) against numeric ( MID + x ). You need to cast your results so that you are comparing like with like. A useful operation is the double unary, --, like this:
=IF(--(MID(A1,3,1))=(MID(A1,2,1)+1),1,IF(--(MID(A1,3,1))=(MID(A1,2,1)-1),1,IF(--(MID(A1,3,1))=--(MID(A1,2,1)),2,0)))

 

[GlennUK]

I think I might have just answered my own question:


I added the following changes:

=IF((--MID(A1,3,1))=(--MID(A1,2,1)+1),1,IF((--MID(A1,3,1))=(--MID(A1,2,1)-1),1,IF((--MID(A1,3,1))=(--MID(A1,2,1)),2,0))))


Does this mean that the left, mid, and right functions return text, even if they are searching a number?

Yes, they do.