Pardon me while I ramble... Sometimes when I'm using Excel I often wonder if the developers realized just how much could be done by combining various "simple" functions.

Did they just come up with a bunch of functions, all of which do a single thing and it's by happy coincidence that very powerful tasks can be performed by combining them, or did they have all of these various combinations in mind? Sure, I realize that some of these functions were meant to be combined, but I wonder if the developers realized the true potential of their work.

For example, I came across one of those situations earlier today in this very forum.

Excel provides an INDEX function to pull data from an array; it provides ROW and COLUMN functions to determine what Row or Column a formula is in; it provides an INT function to return the integer portion of a number and of course it provides the very basic arithmetic operations such as addition, subtraction, multiplication and division.

Earlier today, I came up with this formula to convert a 3 x 3 array of values into a single column:

=INDEX(Sheet1!$A$1:$C$3,COLUMN()+INT(ROW()/3.1),ROW()-INT(ROW()/3.1)*3)

Put this in A1, drag it down and you get the values from Sheet1!A1:A3, row by row, in single column.

Somehow I can't believe that the developers envisioned the need for such a construction, yet here was a situation where that specific combination of these 4 functions and some arithmetic operations provided a solution that isn't really even what a spreadsheet is all about.

It's not like we were adding columns of numbers or really even looking up a specific piece data in an array - although that's actually what we were doing, just not in the typical spreadsheet lookup manner.

Maybe it's just me, but I think it's pretty amazing that by combining seemingly unrelated functions we can produce some very powerful results. It makes me wonder if the developers had any idea that their product would be used in this manner.

Your level of expertise is much higher than I can dream of acquiring. What you explained above, although very interesting, is way over my head and suspect most others too.

Never got the hang of arrays never mind the things that you can do with Excel. I used to use it alot within my job functions but do not in my current position.

I truely enjoy the help you provide people on this site. I am impressed with your macro handywork. You are a valued asset here.

Thanks for coming here.

Best regards,

Bryan

Thank you very much for the kind words. I truly was not fishing for compliments, but it was much appreciated in any case.

I'm quite serious about my curiosity as to the thought process (or pure luck?) that allows seemingly unrelated functions to work so well together.

re:" is way over my head and suspect most others too."

Although it may seem so, in reality it's not all that complicated. It's actually very logical.

Briefly...

The array in this case is really just what some people call a table. e.g. 9 values in a range, say A1:C3. 3 rows by 3 columns.

The INDEX function can pull a number from this array if you give it a row/column intersection.

INDEX(A1:C3,1,1) returns the value at the intersection of Row 1/Column 1

of the array. In this case it's A1, but if the array was D2:H5, the intersection of Row 1/Column 1 would be D2.The point of this thread was that Excel allows you to use other functions to "build" the arguments that the functions use.

e.g. Instead of using "1" as the

row_numargument, I can use SUM(2,-1) which evaluates to 1, or I could use VLOOKUP to grab a row number for another array/table.In the case of the formula I posted above, I use the ROW() and COLUMN() functions to figure out what cell the formula is in and then adjust that value via arithmetic operations and the INT function to get the actual row/column intersection I'm looking for.

It's that combination of functions that I find so intriguing.

I doubt the developers foresaw the need to transpose a 3 x 3 array into a single column, yet they provided the exact set of functions, when arranged properly, that do just that.

Was that planned functionality or divine intervention? ;-)

Hi DerbyDad03, I doubt that the initial developers realized the enormous possibilities of Excel's formulas when they started. The linking of sometimes many functions together is an extremely powerful tool.

Just out of interest I looked at my first copy of Excel - it was Excel 4 and it came on five 1.4Mb floppies !

Regards

I first used Excel on a XENIX box when it was called Multiplan, and even then, it was surprising what you could do with it.MIKE

Ask Your Question

Weekly Poll

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History