Question on array formula's

[Xiq]

Hi everyone!

Just a question to learn more about the possibilities with arrays. I would like to know if there is another way to my current solution in my array formula.

Below are two arrays I want to multiply. As you can see I need the uneven positions multiplied by 1, and the even positions by 3.

{"9";"7";"8";"3";"4";"5";"6";"7";"8";"9";"2";"1"}*{1;3;1;3;1;3;1;3;1;3;1;3}

Currently the right side array is hard coded for 12 numbers. Is it possible to get this dynamic in an easy way?

I know I can do it the long and ugly way... something in the lines of:

=IF(ISEVEN(ROW(A1:LEN(ISBN#)),3,1)

(Just out the top of my head, please don't mind the mistakes )

Do I make sense?

 

[NARAYANK991]

Hi ,

An ISBN is either 10 character or 13 character ; you have mentioned 12 digits ; will this always be 12 digits long ?

Narayan

 

[Luke M]

Not sure about the whole formula as it depends on Narayan's question, but the 1,3,1,... array can be generated as shown here:
=SUMPRODUCT((ISEVEN(ROW(1:12))*2)+1)

 

[Sam Mathai Chacko]

Here's another so-called 'dirty' way, non-dynamic

3^MOD(ROW(2:14),2)

 

[Sam Mathai Chacko]

Luke, you probably meant ISEVEN(ROW(1:12))*2+1 to get that array

 

[Xiq]

Hi Narayan,
My question isn't so much about ISBN numbers, just in general about array formula's. ISBN was just for example.

Just to clarify the 12 numbers: the control number of ISBN13 (the 13th number) is calculated with the first 12 numbers. I have created multiple formula's to do that (including conversion of ISBN10 to ISBN13), no problems there.

My question is more about efficiency/cleanness. I.g.: its better to write 100 * 5, than 5 * 5 * 5 * 5 ... etc.

 

[Luke M]

Luke, you probably meant ISEVEN(ROW(1:12))*2+1 to get that array

Hi Sam. I'm not sure what is different between two two. I was simply showing how the array could be generated inside another function. I just picked SUMPRODUCT since it was convenient. Or was there another point I am missing?

 

[Sam Mathai Chacko]

Oh, OK Luke , sorry for jumping the gun then. I was just trying to conform to Xiq's question about generating an array {1;3;1;3......}

By the way, I did save you the parenthesis nonetheless

 

[NARAYANK991]

Hi ,

Understood. Of course , now I am confused by your next question , whether 100 * 5 is better than 5 * 5 * 5 * 5 ...

Narayan

 

[Luke M]

Ha ha, so you did Sam.

 

[Xiq]

Thanks for the responses :D
Your solutions all go the same direction how I would have guessed (though way better)

But what I'm looking for (if there is such a way) is more like:
I want to generate an array with a sequence of {1;3} over and over again -- where all uneven positions are multiplied by 1 and even by 3 (in this example) -- but for 100 to 1000 characters (depending on a magical variable or something )

Can you, with an easy method, generate the needed array?
Like {1;3} x 100 that gives you {1;3;1;3;1;3 .... etc. }

Or lets make the sequence bigger: {1;2;3;4;5} and I want to easily extend the array to 10 times the sequence in 1 array. Resulting in:

{1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5}

Afterall, you only want to repeat something.

 

[NARAYANK991]

Hi ,

Let us assume the following named ranges :

1. Array_to_be_repeated : =Sheet1!$J$1:$J$6 ; I have entered the following values in this range - 3,7,1,0,6,22

2. Repetitions : =Sheet1!$K$1 ; I have entered 10 in this cell.

3. Rows_ : =ROW(OFFSET(Sheet1!$A$1,,,Repetitions*ROWS(Array_to_be_repeated),1))

4. Final_Array : =N(OFFSET(Array_to_be_repeated,IF(MOD(Rows_,ROWS(Array_to_be_repeated))=0,ROWS(Array_to_be_repeated),MOD(Rows_,ROWS(Array_to_be_repeated)))-1,))

The named range Final_Array will contain : {3;7;1;0;6;22;3;7;1;0;6;22;3;7;1;0;6;22;3;7;1;0;6;22;3;7;1;0;6;22;3;7;1;0;6;22;3;7;1;0;6;22;3;7;1;0;6;22;3;7;1;0;6;22;3;7;1;0;6;22}

Narayan

 

[Xiq]

Wow Naryan, that is one huge bad ass formula

But I guess there is not a real easy way then

Thanks a lot for all your feedback and awesome formula's! I'll take some notes

 

[Luke M]

There's obviously many different ways to generate the variable, but for the first part of the question, where you array is just two different numbers, your formula structure could be something like:
=SUMPRODUCT(3^MOD(ROW(INDIRECT("2:"&MyVariable+1)),2))

Where MyVariable is the number indicating how many numbers you want in your array.