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

 

[Desu Nota from Columbus]

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

BR,
Jonathan

This was an interesting concept and I just got it working and wanted to post what I did just because I made the effort.

This was my work around (which was much less effective than Glenn's )

B1: =IF(--MID(A1,2,1)=--LEFT(A1,1)+1,1,IF(--MID(A1,2,1)=--LEFT(A1,1)-1,1,IF(--MID(A1,2,1)=--LEFT(A1,1),2,0)))

C1:
=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)))

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

e1:
=sum(b1:d1)


Copy all formula down the column!!


Better late than never!

 

[Gerald Higgins]

Desu - I think the problem is in the middle.

Mid(A1,3,1) returns a text value of 6.
Mid(A1,2,1) returns a text value of 7.
BUT, Mid(A1,2,1)-1 returns a numeric value of 6, because Excel guesses you want to do a subtraction.
But it doesn't make the same guess when you try and compare the text 6 with the numeric 6.
Does this make sense ?
See how I got round that problem in my suggestion.

Edit to add - sorry, I'm too slow today

 

[JonathanWootton]

Thanks all for your efforts - I have been tinkering and used Glenns - with some slight tweaks, due to me not giving full details, but in essence it's spot on.

So thanks again!
JW

 

[GlennUK]

That's great! Glad to help.