• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hit the wall with INDEX

juanito

juanito

Member
I have an 2D array - in the name manager, not the grid. My model uses INDEX to return discrete columns of this array. So if the array's name is "d", then to return the third column I have:
INDEX(d,,3)

(This is using the awesome power of omitting the row argument in INDEX - you get them all!)

But now I have a problem. Depending on a number the dashboard user selects, I want to return a number of columns (the columns are individual months which after processing will be passed to a chart). Say she selects five. My model generates a vector called "col_vec" of {1,2,3,4,5} and uses the following formula:
INDEX(d,,col_vec)

And it doesn't work! Looks like INDEX won't pick up an array, and so won't return a series of vectors.

Can anyone think of a workaround for this?

- Juanito
 
Hi Juanito..
Your formula Index(d,,3) will return an array of 3 column of d array if you press Ctrl+Shift+enter.

What you should do if you want to display the value of 5 colunm of d array you should use a number incrementer which will return 1,2,3,4,5 one at a time. And copy the formula over 5 cells.

Advise if I am unable to make it clear.

Regards!
 
Hi Somendra -

Thanks for looking at this.

Yes, my formula INDEX(d,,3) works fine. (It's in the name manager, which is the same as array-entered.)

But I can't get it to work when I pass it a vector as you say of {1,2,3,4,5} as the column argument. I believe this is a limitation of INDEX.

- Juanito

Edit: I should add that this formula is also in the name manager: it isn't copied over cells because the number of cells to copy across can vary with the user selection described above
 
Last edited:
@juanito
Yes I think this is a limitation. But this can be overcome, as I suggested earlier. By a using an increment function you can select upto input value say from 1 to 5 and get the value in different cell. Try that.

Regards!
 
Somendra -

I don't see the difference between what you're suggesting and my example above:
=INDEX(d,,col_vec)
Where col_vec is a vector of 1 to 5

Could you illustrate a little more?

Thanks!

- Juanito
 
OK here it is. To explain a little:
  1. Almost everything is being processed not in the grid but in the name manager
  2. I have a vector of values in name "_d_oee". Each value has a date associated with it held in vector of identical length "_d_dates"
  3. I have a 2D array of booleans, number of rows same as the vectors above, and one column for each month, in name "oee_mth_array". For each column (month), value is 1 where the associated date matches the month, and 0 otherwise
  4. The number of months examined varies by user selection. There is a vector "oee_num_mths_vc" which increments from 1 to the total number of months selected. The number of months equals the number of columns in the array just described. This is the vector I wanted to use with INDEX!
  5. I want to calculate the average of the values for each month, and pass the results to a chart
  6. I'm using the name manager rather than cells, because the number of user-selected periods can vary
Let me know if I need to explain more/better - thanks!

- Juanito
 

Attachments

  • juanito file.xlsx
    59 KB · Views: 24
There isn't a name... I tried to do it but the INDEX function didn't return the discrete vectors as I hoped. It's where I got stuck. That's why I'm wondering if the forum can help me find a workaround.

I want to multiply "_d_oee" by each column of "oee_mth_array", to obtain each month's OEE values. Then I would perform calculations such as obtaining the average of each column.

Hope that's clear!

- Juanito
 
Hi Juanito...

Please see the attached file.

Since I could not achieved in resolving your core issue of passing a vector as column number argument in INDEX function. I tried to slove your issue through other means.

I had use VBA code, define dynamic named range, dynamic chart to create this. Just go through it and see this is what you want.

Advise if you have any issue.

Regards!
 

Attachments

  • juanito file(modified).xlsm
    91.4 KB · Views: 11
@juanito ...
One thing more I had observed that some cells in your data of oee were blank and when I am trying to calculate the average with formula it was differing with manual calculation. So I replace all blank cells with 0%, to match the calculation. Hope this will not affect your data.

Regards!
 
Hi Somendra - I really appreciate your analysis and support. I was hoping to find a formula-based solution as I'm sure you understand, but your VBA approach is very interesting and I will further analyse over the next days.

Very best wishes and Season's Greetings,

- Juanito
 
Hi, juanito!

Give a look at the uploaded file. I defined 2 sample arrays in worksheet data:
IList: F2:F6, 5x1 elements, array, value {1;3;2;5;4}
Vlist: H2:K6, 5x4 elements, matrix, value {10\20\60\240;30\60\180\720;20\40\120\480;50\100\300\1200;40\80\240\960}

In M2 I put this array formula, just for testing and debugging purposes, and copied down thru M6:
=INDICE(VList;0;IList) -----> in english: =INDEX(VList,0,IList)
which even if it displays:
10, corresponding to {10;30;20;50;40}
if you debug cell by cell of column M, you'd get:
{10;30;20;50;40}, for IList = 1, i.e. 1st column of VList
{60;180;120;300;240}, for IList = 3, i.e. 3rd column of VList
{20;60;40;100;80}, 2th
#¡REF!, since there's no 5th column in VList, just 4
{240;720;480;1200;960}, 4th

Is that what you were looking for?

Regards!
 

Attachments

  • Hit the wall with INDEX - juanito file (for juanito at chandoo.org).xlsx
    62.3 KB · Views: 23
Ah!
Now that is very interesting... and quite mysterious.
I think that will cover my needs: thanks very much, SirJB7!

But the mystery (and this is what was confusing me) is this: cell M3, for example, is displaying 10. However, as you have shown (and I've verified with F9), in fact it contains the vector {60;180;120;300;240}, which is what I wanted it to contain. Have you any idea why it is displaying a value which isn't in the vector (I would expect it to display the first value)? I've never come across this before.

- Juanito
 
@SirJB7

Please give an insight of the formula and values you used. It is very interesting solution for all of us to learn.

@juanito ... Can you explain how this soultion is releted to you soultion of getting averae of each month for the selected drop-sown period.

Regards!
 
Hi Somendra! I think you can see what SirJB7 is saying in the file he has uploaded. You should also be able see what is still puzzling me: I had tried exactly the same approach, however, because the array formula was displaying different values from what it actually contains, I believed I had "hit the wall"!

As for the solution to what I was looking for, I believe I can take SirJB7's formula and then perform the operations I want to make - average, for example - on the returned array. For example, if I take SirJB7's formula {=INDEX(VList,0,IList)}, and edit to {=AVERAGE(INDEX(VList;0;IList))}, it returns the exact vector of averages which I wanted. I haven't tested yet in my original model but I'm pretty confident it will work.

Your assistance in this thread has been much appreciated, Somendra, and the solution you offered is a great workaround: however, it turns out I may not need a workaround after all!

Best, Juanito
 
juanito said:
But the mystery (and this is what was confusing me) is this: cell M3, for example, is displaying 10. However, as you have shown (and I've verified with F9), in fact it contains the vector {60;180;120;300;240}, which is what I wanted it to contain. Have you any idea why it is displaying a value which isn't in the vector (I would expect it to display the first value)? I've never come across this before.
Click to expand...
Hi, juanito!
Not a clue at all, I haven't done this before, just tried after reading your post.
Regards!
 
Somendra Misra said:
@SirJB7

Please give an insight of the formula and values you used. It is very interesting solution for all of us to learn.
Click to expand...
Hi, Somendra Misra!
As juanito wrote I don't know if there's much more to write than what I've posted, all the mysterious and interesting stuff can and should be found debugging the formulas at column M with F9, comparing the actual values with what's displayed.
I chose the short sample of VList matrix and IList vector because the original arrays were of 2K elements and difficult to handle and analize (all zeros and ones).
I actually didn't care too much -even I got surprised- for the displayed results since juanito will be using them with in named formulas, but I'm gonna play a bit more with another options just to check if I could arrive to further conclusions.
Regards!
 
Hello Juanito,

I am not clear about the requirement. INDEX can't return multiple row or column values in array. means,

=INDEX(A1:A3,{1;2;3},1)

Will return first value A1. But when you enter them in 3 rows as Array Formula will return correct values. same as columns,

=INDEX(A1:C1,1,{1,2,3})

But, OFFSET can do it

=OFFSET(A1,{0;3;5},0)

Will return an array of A1, A4 & A6 values. You can use them also inside the formulas. In some case OFFSET require derefernce, so if it is number use with N() if it is text results, use with T() functions.

Or,

=LOOKUP({1,4,6},ROW(A1:A10),A1:A10)

This will return value of A1, A4 & A6 in array, regardless of its format, texts or number.

Back to your issue:

If you are looking to AVERAGE between user selected months, why can't you use simple AVERAGEIFS?

=AVERAGEIFS(_d_oee,_d_dates,">="&oee_start_mth,_d_dates,"<="&oee_end_mth)

Am i missing something?
 
SirJB7 said:
In M2 I put this array formula, just for testing and debugging purposes, and copied down thru M6:
=INDICE(VList;0;IList) -----> in english: =INDEX(VList,0,IList)
which even if it displays:
10, corresponding to {10;30;20;50;40}
if you debug cell by cell of column M, you'd get:
{10;30;20;50;40}, for IList = 1, i.e. 1st column of VList
{60;180;120;300;240}, for IList = 3, i.e. 3rd column of VList
{20;60;40;100;80}, 2th
#¡REF!, since there's no 5th column in VList, just 4
{240;720;480;1200;960}, 4th
Click to expand...
Hi, Haseeb A!
My 1st time doing such a thing just to emulate juanito's method, and I never saw that, it really surprised me.
If you have a little of time, please go thru it; I'd be glad to read you on this.
Regards!
 
Hello SirJB,

If you hard code IList values in M2 & copy down.

=INDEX(VList,0,{1;3;2;5;4})

When we do F9 calculation, can see only first column values in all rows. I think when we use as cell reference, INDEX using appropriate n row number when copy down.

If we use >0 row number in INDEX,

=INDEX(VList,1,{1,3,2,5,4}) or =INDEX(VList,1,IList)

can see only first value in the range.
 
You must log in or register to reply here.
Back
Top