# Archive of Mr Excel Message Board

Back to Forms in Excel VBA archive index
Back to archive home

## range of numbers problem

Posted by Chris D on February 02, 2002 3:42 PM
Hi all,

I'm trying to multiply each digit of a 9-digit number by 1,2,3,4,5,6,7,8,9 respectively and sum the results :

(where a1 holds the 9-digit number 555847924)

=SUM(MID(+A1,{1,2,3,4,5,6,7,8,9},1)*{1,2,3,4,5,6,7,8,9})

it gives the correct result of 239, but I'd like to shorten the formula thinking I could use the range 1:9 instead of 1,2,3,4,5,6,7,8,9 :

=SUM(MID(+A1,{1:9},1)*{1,2,3,4,5,6,7,8,9})

which it doesn't like. Is it possible to use a range of numbers like this ?

ie =if(or(a1={10:25}),"within range","outside range")

many thanks
Chris

 Check out our Excel Resources

## =SUMPRODUCT((A1:A9),{1;2;3;4;5;6;7;8;9})

Posted by Ivan F Moala on February 02, 2002 11:09 PM

## doesn't work :-(

Posted by Chris D on February 03, 2002 2:07 AM
thanks Ivan, it didn't quite work though : I think I was a bit misleading.....

say I wanted to check if the value in A1 was equal to anything from 5 up to 15

I know I could use if(or(a1={5;6;7;8;9;10;11;12;13;14;15}),"yes","no")

but is there a way to represent that "range" of numbers easier than having to specifiy them all individually ?

(I don't want to use and(a1>=5,a1<=15) either if possible)

many thanks
Chris

## Re: doesn't work :-(

Posted by Aladin Akyurek on February 03, 2002 2:53 AM
Chris --

I'm a bit amazed...

> thanks Ivan, it didn't quite work though : I think I was a bit misleading.....

YES, you were. :) say I wanted to check if the value in A1 was equal to anything from 5 up to 15

> I know I could use if(or(a1={5;6;7;8;9;10;11;12;13;14;15}),"yes","no")

NO, you can't. As is, it will not work. but is there a way to represent that "range" of numbers easier than having to specifiy them all individually ?

NO need for that, I'd think. (I don't want to use and(a1>=5,a1<=15) either if possible)

WHY not?

=IF(AND(A1 >= 5, A1 <= 15),"yes","no")

or

=IF(AND(A1 >= 5, A1 <= 15),1,0) which is equivalent to:

=(AND(A1 >= 5, A1 <= 15)+0

I believe the above to be right approach to the question using an array formula like what follows:

=ISNUMBER(MATCH(A1,ROW(INDIRECT("5:15")),0))

which needs control+shift+enter, instead of just enter.

Regards.

## Re: doesn't work :-(

Posted by Chris D on February 03, 2002 3:28 AM

back to my original problem then : I want to multiply the first digit by 1, the second digit by 2 etc etc the 9th digit by 9 and add up the results

so 555847294 will result in 239

trying to use your row(indirect("number range"
as follows :

=SUM(MID(+A1,ROW(INDIRECT("1:9")),1)*{1,2,3,4,5,6,7,8,9})

I get 225 entered normally, and 2205 array-entered

(the 1:9 is exactly what I meant, not having to type in all the numbers 1 to 9)

am I getting closer ?
many thanks
Chris

## Multiplication question

Posted by Aladin Akyurek on February 03, 2002 3:56 AM
back to my original problem then : I want to multiply the first digit by 1, the second digit by 2 etc etc the 9th digit by 9 and add up the results so 555847294 will result in 239 trying to use your row(indirect("number range"

YES, I know you had your first/central question in mind. That's the reason I wrote up that MATCH formula with ROW+INDIRECT to give you a clue. I wanted you to keep your original

=SUM(MID(A1,{1,2,3,4,5,6,7,8,9},1)*{1,2,3,4,5,6,7,8,9})

without a + before A1, which is not needed. Are you using formula wizard to devise your formulas by any chance?

Since you insist, array-enter:

=SUM(TRANSPOSE(MID(A1,ROW(INDIRECT("1:9")),1))*{1,2,3,4,5,6,7,8,9})

Chris, I don't any advantage of using this array-formula instead of your ordinary SUM formula, which is less expensive.

Regards,

## Got it : =SUMPRODUCT((MID(+A1,ROW(INDIRECT("1:9")),1)*{1;2;3;4;5;6;7;8;9})) thanks Aladin, thanks Ivan :-)

Posted by Chris D on February 03, 2002 3:57 AM
back to my original problem then : I want to multiply the first digit by 1, the second digit by 2 etc etc the 9th digit by 9 and add up the results so 555847294 will result in 239 trying to use your row(indirect("number range"

## Re: Multiplication question

Posted by Chris D on February 03, 2002 4:05 AM
yeah, just entering the 1,2,3,4 etc in this case is probably easier, as you say

I just knew there must be a more efficient way of entering the range 5 to 15 (say) rather than having to enter all ten numbers

checking, say, account codes if(or(account number={123;124;125;126;127;128;129;130 etc etc is tedious if they are all in a range of numbers 123 to 200)

as you can tell, I've only just "learned" about this or(a={1;2;3;4} rather than using a=1,a=2,a=3,a=4 so am a bit green

:-)

## Re: Got it : =SUMPRODUCT((MID(+A1,ROW(INDIRECT("1:9")),1)*{1;2;3;4;5;6;7;8;9})) thanks Aladin, thanks Ivan :-)

Posted by Aladin Akyurek on February 03, 2002 4:12 AM
No, unless you array-enter this SUMPRODUCT formula. I don't think that is necessay. I'd suggest using the pure array-version that I proposed.

Cheers.

## Re: Got it : =SUMPRODUCT((MID(+A1,ROW(INDIRECT("1:9")),1)*{1;2;3;4;5;6;7;8;9})) thanks Aladin, thanks Ivan :-)

Posted by Chris D on February 03, 2002 4:34 AM
both give the same answer, but mine freezes the computer, so it can't be healthy !

is the transpose correcting the R1C1 1:9 logic ?

looks like I need to log off and read the "indirect" help files

thanks Aladin, sorry to waste your time a bit too much this morning !

## Recap

Posted by Aladin Akyurek on February 03, 2002 5:45 AM
> both give the same answer,

Right. I missed the fact that you transposed the second constant array manually:

from {1,2,3,4,5,6,7,8,9} to {1;2;3;4;5;6;7;8;9}.

In this form, SUMPRODUCT is OK.

but mine freezes the computer, so it can't be healthy !

It shouldn't freeze your computer. is the transpose correcting the R1C1 1:9 logic ?

What is needed is that the first array and the second constant array are both row or both are columns.

TRANSPOSE in the array-formula that I suggested was changing the first array from the columnar form into a row form. looks like I need to log off and read the "indirect" help files thanks Aladin, sorry to waste your time a bit too much this morning !

Well, it's an interesting topic worth discussing.

To recap:

We have your original SUM formula with two constant arrays. And, transposing manually we have:

=SUMPRODUCT(MID(A1,ROW(INDIRECT("1:9")),1)*{1;2;3;4;5;6;7;8;9})

or

{=SUM(MID(A1,ROW(INDIRECT("1:9")),1)*{1;2;3;4;5;6;7;8;9})}

If you want to work with variable length numbers in A whose digits must be multiplied with {1;2;...;n} where n is the length of the number of interest, use:

=SUMPRODUCT(MID(A1,ROW(INDIRECT("1:9")),1)*ROW(INDIRECT("1:"&LEN(A1))))

## Re: Recap

Posted by Chris D on February 03, 2002 7:45 AM

It may seem a bizarre exercise to have to carry out, so I'll explain :

I work a lot with VAT (MwSt in Germany, TVA in France etc etc) and one of my checks is to look at a VAT registration number on an incoming invoice and ensure it exists mathmatically.

(I forget the actual numbers as it's on my work machine, but for illustration purposes it's something like : )

1st digit x 8
2nd digit x 7
3rd digit x 6
etc
etc
etc
etc
9th digit x 1

all summed together should *always* be a number which is divisible by 94

ie = 94 or 188 or 282 etc etc

if the digits do not equal these amounts, there is a suspicion about the VAT number and it needs to be investigated for fraud.

I had designed an easy worksheet where the user inputted each digit in a cell and the formulae were basically as per above, but wanted to simplify it to a single cell formula that could be copied down a range of our invoice listings per month to check each VAT number without any user input.

Not sure if the structure is EU-wide though, I'll have to check that out and redesign appropriately.

Thanks again
much appreciated
Chris
:-)

## Re: Recap

Posted by Aladin Akyurek on February 03, 2002 8:03 AM
Chris, It's an interesting app. The formula is there. It seems you have to put the second contant array in descending order and place the whole thing within the MOD func. Nice app.