Hit the wall with INDEX

[SirJB7]

Hi, Haseeb A!
Thanks for joining us. Interesting feature, or bug, or something, but interesting at last. If I hardcode IList values INDEX function with row set to zero retrieves always the 1st column, as expected since it doesn't handle arrays. But if I use the named range IList, even if it displays the 1st element of 1st Ilist value entry (1, for 10), it still holds each related column from VList regarding IList entries.
Regards!

 

[Somendra Misra]

Hi, SirJB7 & Haseeb A!,

Earlier when problem was first posted by OP I tried Hard coding in like {1;2;3;4}, but result was the same as Haseeb A told. While yesterday experimenting with it I observed two things:

1. If after entering the formula =INDEX(IList,0,Vlist) you just enter and do not Ctrl+Shift+Enter than the value which are coming are present on diagonal of data array unlike to 10 in each cell with CSE. Over to that if you calculate in cell with F9, it shows complete column. Amazing!

2. If you are entering this formula in same rows as data is there then the formula is evaluating the columns hidden in formula in form of an array, but if you copy this formula to any other part of sheet it is displaying #value error if you just press enter and if you press CSE than in cell 10 is appearing but if evaluate formula using F9 it is showing #Value error !!!!

Again this is behaviour is superb, because if you use the array return by this formula in say AVERAGE function the AVERAGE function had to be in same rows as data is there, I mean if Data is in B2:E5 than formula should be in say F2:F5. Just check this out.

CSE: Ctrl+Shift+Enter.

Regards!

 

[Somendra Misra]

@juanito

Hey had you checked your result from the formula given by SirJB7?

Regards!

 

[KenU]

@juanito

I have taken a stab at this in the attached workbook. I am using MMULT to create the sums and counts for the averaging. It is presently set up to ignore zero values so as not to reduce the average due to blanks.

I have made some changes to your data to remove text entries from the "_d_oee" values to avoid errors in the calculations. I modified two of your defined names and added four new names.

All changes and further explanation are detailed on a new sheet called NameChanges.

I hope this solves the problem, or at least helps.

Regards,
Ken

 

[juanito]

Friends - thanks for all your contributions over the last days. For some reason I wasn't getting the advices and have only just read the last few posts (thanks for tagging me, Somendra - that's what got through).

Time to see in 2014 now, so I'll catch up tomorrow.

Best to all, and Happy New Year!

- juanito

 

[juanito]

@SirJB7, @Somendra Misra, @Haseeb A: I have also been experimenting with the behaviour of INDEX() and unearthing erratic, or unexpected, outcomes. I will upload a file with my findings later this week and it would be great if we can continue this shared analysis (*). INDEX() is such an incredibly useful function for handling datasets that it will be enlightening to come to a conclusion.

@KenU: you seem to be new round here, so welcome to the forums first of all! Your first contribution appears to be pretty impressive, heavy lifting indeed, and I believe I can learn a lot from your use of MMULT (a function I have never quite got round to engaging with).

My time continues to be limited this week but I will do my best to look in depth as soon as possible at all your contributions and keep the momentum going.

Feliz Año Nuevo a Todos

- juanito

(*) Somendra: can you upload a workbook with examples of your surprising findings? Thanks!

 

[juanito]

@KenU: I have finally had a chance to look at your file. Really interesting and instructive use of vectors and MMULT (and very clearly documented too): this will be extremely useful to me as I develop my model. Thank you!

- juanito

 

[juanito]

@KenU and others. I ran into another issue. KenU created a vector in his reworked model which looks exactly what I wanted. However, when I try to plot this vector in a chart, I only get the first value returned, instead for the entire vector.

(See uploaded workbook.)

If I visualise the SERIES formula, select the x axis (month names) and press F9, I get the full 6 vector elements. However, when I do the same for the y axis (values), I only see the one value (the first in the vector).

Grateful for any ideas, thanks.

- juanito

 

[KenU]

@juanito

I believe I have corrected the problem in the attached workbook. I mistakenly reported one formula as returning a a matrix (or 2D array) when it would only do so if using the worksheet grid. As a named formula, it was dimensioned as a vector. I have modified that formula (after a lot of thought and some trial and error) to get the needed matrix.

The chart will now work using the named formula.

Regards,
Ken

 

[juanito]

@KenU: thank you once again and this issue now looks totally fixed!

I will explain a little more for the benefit of other readers: once again we ran into a case where INDEX returned an array in the name manager which in actual fact was of a different size than it looked when array-entered across the grid. And that had me totally stuck.

Ken's workaround was to use matrix multiplication - MMULT - instead of INDEX to work with my dataset and now everything's hunky-dory.

The moral of the story: beware when using INDEX in the name manager instead of the grid.

- juanito