range of numbers problem

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

doesn't work :-(

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

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

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

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

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

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

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

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

> 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

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

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