checking formula.. need help

[verlaine]

Hi all,

I need a formula or macro to do that:

user introduce 14 numbers (6+8):
49927312345678

The formula must double the value of alternate digits beginning with the penultimate right hand digit and working left.

4 9 9 2 7 3 1 2 3 4 5 6 7 8
x x x x x x x
2 2 2 2 2 2 2
= = = = = = = = = = = = = =
8 9 18 2 14 3 2 2 6 4 10 6 14 8

I need a total with the results (individual digits)

8 + 9 + (1 + 8) + 2 + (1 + 4) + 3 + 2 + 2 + 6 + 4 + (1 + 0) + 6 + (1 + 4) + 8 = 70

and finally divide the total by ten

70/10 = 7

If someone can help me...

 

[verlaine]

Thanks for all your replies.. but I'm not sure how to use this or if my request was clear...

I've got two sheets in my workbook. One where the user put the numbers and one with the data.

The formula has to search where the corresponding data are in the second sheet.

If the user put 40022812345678, the formula has to check with the first 4 digits which line in second line is. In second sheet the column A and B are number A is from and B is to.
For this it will be : A : 400212 and B : 400299 so this line will correspond to the input 40022812345678.

Thks

 

[onlyadrafter]

Hello rrdonutz,

Yes correct my formula was incorrect as I left an '&' in their and not '+'.
Here is the formula corrected.

=(B6+D6+F6+H6+J6+L6+N6+IF((A6*2)>=10,LEFT((A6*2),1)+RIGHT((A6*2),1),A6*2)+IF((C6*2)>=10,LEFT((C6*2),1)+RIGHT((C6*2),1),C6*2)+IF((E6*2)>=10,LEFT((E6*2),1)+RIGHT((E6*2),1),E6*2)+IF((G6*2)>=10,LEFT((G6*2),1)+RIGHT((G6*2),1),G6*2)+IF((I6*2)>=10,LEFT((I6*2),1)+RIGHT((I6*2),1),I6*2)+IF((K6*2)>=10,LEFT((K6*2),1)+RIGHT((K6*2),1),K6*2)+IF((M6*2)>=10,LEFT((M6*2),1)+RIGHT((M6*2),1),M6*2))/10

 

[fairwinds]

Hi,
Re your second question;

Please clarify:

How can 400212 be 4 digits / numbers? ist 400 2 1 2 or...? and is it always split like that?

You say 1 is 5th number in 40022812345678. Will the "5th" number always be in pos 7 ???

 

[verlaine]

Sorry I made a mistake...

this is 6 digits + 8 digits...

so it's the first 6 digits and not 4 ...

 

[fairwinds]

Don't think I get it all, but maybe as a start:

A & B column are values, Dcolumn is text.

Book2
	A	B	C	D	E
1
2	500222	589999	1	52022212345678	1
3	590000	609999	2
4	700000	800000	3
Sheet3