checking formula.. need help

verlaine said:

...user introduce 14 numbers (6+8):
49927312345678...

Click to expand...

In 1 cell, 2 cells, or 14 cells?

sorry in 14 cells...

thanks

Sorry,

I should think so too, I just did it for one cell.

=(MID(A1,2,1)+MID(A1,4,1)+MID(A1,6,1)+MID(A1,8,1)+MID(A1,10,1)+MID(A1,12,1)+MID(A1,14,1)+IF(LEFT(A1,1)*2>=10,LEFT((LEFT(A1,1)*2),1)+RIGHT((LEFT(A1,1)*2),1),LEFT(A1,1)*2)+IF(MID(A1,3,1)*2>=10,LEFT((MID(A1,3,1)*2),1)+RIGHT((MID(A1,3,1)*2),1),MID(A1,3,1)*2)+IF(MID(A1,5,1)*2>=10,LEFT((MID(A1,5,1)*2),1)+RIGHT((MID(A1,5,1)*2),1),MID(A1,5,1)*2)+IF(MID(A1,7,1)*2>=10,LEFT((MID(A1,7,1)*2),1)+RIGHT((MID(A1,7,1)*2),1),MID(A1,7,1)*2)+IF(MID(A1,9,1)*2>=10,LEFT((MID(A1,9,1)*2),1)+RIGHT((MID(A1,9,1)*2),1),MID(A1,9,1)*2)+IF(MID(A1,11,1)*2>=10,LEFT((MID(A1,11,1)*2),1)+RIGHT((MID(A1,11,1)*2),1),MID(A1,11,1)*2)+IF(MID(A1,13,1)*2>=10,LEFT((MID(A1,13,1)*2),1)+RIGHT((MID(A1,13,1)*2),1),MID(A1,13,1)*2))/10



Will have to change it now!!!!

Hello,

Try this formula for numbers inputted in 14 different cells

=(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

You will need to change cell refs as required.

Thank you very much !

I have to do another check more complicated...

I've got a sheet with :

Column A: code of 4 numbers (ex: 500222) I mean from 500222
Column B: code of 4 numbers (ex: 589999) I mean to 589999
Column C: 10 or 11
Column D to Q : numbers

The users introduce a number of 14 digits in one cell (or one number per cell if it's easier..).

I've to check line where the first 4 numbers are. If its 52022212345678, the first 4 numbers ( 520222) belong to line one (500222-589999). So I have to multiply the 1 (number in position five) with the number in cell D1, 2*E1, 3*F1,...
and the total should be divide by the numbers in column C for the same line..

Is this possible ?

Hi verlaine,

I'm not quite sure I understand your last request. However, here are some formulas to help you with your original request. If the number is in one cell (say A1), then either of these formulas will work:

=SUMPRODUCT(CEILING(MOD(MID(A1,ROW(INDIRECT("1:14")),1)*(1+MOD(ROW(INDIRECT("1:14")),2)),9.5),1))/10

or:

=SUMPRODUCT(CEILING(MOD(MID(A1,{1;2;3;4;5;6;7;8;9;10;11;12;13;14},1)*(1+MOD({1;2;3;4;5;6;7;8;9;10;11;12;13;14},2)),9.5),1))/10

If there are 14 numbers in 14 columns, then this:

=SUMPRODUCT(CEILING(MOD(A1:N1*(1+MOD(COLUMN(A1:N1),2)),9.5),1))/10

or in 14 rows:

=SUMPRODUCT(CEILING(MOD(A1:A14*(1+MOD(ROW(A1:A14),2)),9.5),1))/10

--Tom

verlaine said:

sorry in 14 cells...

Click to expand...

If the range is A1:N1 (a horizontal range)...

=SUMPRODUCT(--(MOD(COLUMN(A1:M1)-CELL("Col",A1)+0,2)=0),1+(MOD(2*(A1:M1),1)/10))

If the range is A1:A14 (a vertical range)...

=SUMPRODUCT(--(MOD(ROW(A1:A13)-CELL("Row",A1)+0,2)=0),1+(MOD(2*(A1:A13),1)/10))

For robustness (as practiced by Aladin), modify the formula I proposed above for A1:N1 to:

=SUMPRODUCT(CEILING(MOD(A1:N1*(2-MOD(COLUMN(A1:N1)-CELL("col",A1),2)),9.5),1))/10

and the formula for A1:A14 to:

=SUMPRODUCT(CEILING(MOD(A1:A14*(2-MOD(ROW(A1:A14)-CELL("row",A1),2)),9.5),1))/10

Aladin, your formulas don't seem to work consistently for values other than the test set, even when modified to include all 14 columns or 14 rows.

Onlyadrafter, your formula doesn't seem to work correctly if the first digit is > 4.

Regards,

Tom