Excel Formula Solution need for plotting 1D Results from 2D list of data

**Lee_SequansUK** · 09-28-2012, 11:50 AM

I am not sure this can be done and I have pretty much given up think it is possible. However, I thought I would throw this query out to anyone listening/reading who may know a novel way of doing this. The idea is to avoid macros because this is the only thing that has limited me through formulation with Excel Environment.

I have attached a copy of the problem with some information on what the expect process is for deciding what to output. If it is not clear then I am happy to provide additional information to help.
I thought this could be solved using the indirect & lookup Excel methods.....but I am no advanced expert but feel I am close to getting it ( without macros )

cheers, Lee

**ChemistB** · 09-28-2012, 02:54 PM

In the 1 dimensional data, shouldn't -6 be included as part of column 1 and then again as the first value in Col 2? -6 is not larger than -6 so should read
-18
-12
-6
0
6
etc.

**ChemistB** · 09-28-2012, 03:29 PM

Assuming I am correct above and you really do mean Larger than and not Equal to or larger than

In A16, enter 0, In B16 dragged right as far as you want to go

=IF(ISNUMBER(B6),IF(ISNUMBER(C6),MATCH(C6,B6:B15)+A16,10+A16),"")

For your 1 dimensional array use this formula dragged down

=INDEX($B$6:$F$15, ROW(A1)-INDEX($A$16:$D$16,MATCH(ROW(A1)-1,$A$16:$D$16)), MATCH(ROW(A1)-1,$A$16:$D$16))
Does that work for you? Attached is my worksheet showing how I sort of reasoned things out.

**Lee_SequansUK** · 10-02-2012, 05:46 PM

Hi ChemistB.

Sorry for the delayed Response. To be honest I did not expect something so quick!
You got it perfectly, this exactly what I wish to do. Many thanks!

I have not yet looked into the detail of your method but once I have digested it I certainly take on board this angle of Excel formulation for Dynamic tabling ( so I do not have to pester the community again !)

I am not quite sure I understand your first point. Each column in the 2D table is meant to contain every increasing values BUT with some "manual" randomness. IT is just my poor copy&paste&modify of columns to make up the 2D table which leads to the same values of -6, 0,6

Once again , Many thanks, Lee

**ChemistB** · 10-03-2012, 12:05 PM

Glad it looks like it's working, Lee.
My first point was that -6 should appear twice in your 1D sequence since it's in Col 1 and at the top of Col 2 and -6 is not larger than -6.