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.

Aladin


Re: doesn't work :-(

Posted by Chris D on February 03, 2002 3:28 AM
thanks Aladin, I'm nearly there....

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,

Aladin


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

:-)

appreciate your time, as always
thanks Aladin


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.

Aladin


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))))

Aladin


Re: Recap

Posted by Chris D on February 03, 2002 7:45 AM
Thanks again Aladin :-)

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.

Aladin

PS. I don't either whether the rule applies thru the EU, although I expect it will.

============ Thanks again Aladin :-) 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



This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.