Strage function in worksheet {=TABLE(I64,)}

  • Thread starter Thread starter mkadon
  • Start date Start date
M

mkadon

I am attempting to understand what appears to be a custom function in a
workbook I was given, author unknown. It seems to be some type of index or
look up but their is no help on it or macro defined for it.

has anyone used or familiar with this? If you need more information I would
be happy to provide it.

{=TABLE(I64,)}

Thanks,
Mark
 
I am attempting to understand what appears to be a custom function in a
workbook I was given, author unknown. It seems to be some type of index or
look up but their is no help on it or macro defined for it.

has anyone used or familiar with this? If you need more information I would
be happy to provide it.

{=TABLE(I64,)}

Thanks,
Mark
Click to expand...

I believe that was a macro function for Excel 4 macro sheets (and perhaps other
versions also). I think the function, as written, defines I64 as the row input
for the table.


--ron
 
Ron Rosenfeld said:
....
I believe that was a macro function for Excel 4 macro sheets (and perhaps
other versions also). I think the function, as written, defines I64 as
the row input for the table.
Click to expand...

It's *NOT* an XLM macro function (XLM macro functions can't be entered in
normal worksheets), and it's been in Excel all the way back to version 1.
It's entered into ranges by the menu command Data > Table. While TABLE may
look like a function and behave like a function, it can't be entered
interactively, and it can't be used in longer formulas. It's a kludge that
provides functionality similar to 123 Release 2's /D(ata)T(able), but with
the added benefit that it's may optionally be refreshed automatically by
recalculation. That's why the Calculation tab in the Options dialog contains
separate options for Automatic and Automatic except tables.

The cells in the column immediately to the left of the array of cells
containing the TABLE call contain the formula(s) applied by TABLE to the
values in the row immediately above this array of cells.
 
It's *NOT* an XLM macro function (XLM macro functions can't be entered in
normal worksheets), and it's been in Excel all the way back to version 1.
It's entered into ranges by the menu command Data > Table.
Click to expand...

How does an "XLM Macro" function different from the XL4 Macro functions which
could be entered on a macro sheet, as I originally described?

TABLE is listed as such in the Function Reference for XL4 (remember when we
used to get printed manuals for MS products?).


--ron
 
Hello, thanks for your insight, you obviously know spreadsheets very well.
I am still a novice and don't completely understand your observation.

Would you mind expanding on your answer a bit further.

"The cells in the column immediately to the left of the array of cells
containing the TABLE call contain the formula(s) applied by TABLE to the
values in the row immediately above this array of cells."

{=Table(E63,E64)}

I take it the TABLE call in this example is E63 and E64? I am not able to
find the formula you referred to?

This is a tough one for me! I guess I need it explained in a fashion for a
true beginner!

Thank you for your time and assistance.

Mark
 
Hello, thanks for your insight, you obviously know spreadsheets very
well. I am still a novice and don't completely understand your
observation.

Would you mind expanding on your answer a bit further.

"The cells in the column immediately to the left of the array of cells
containing the TABLE call contain the formula(s) applied by TABLE to
the values in the row immediately above this array of cells."
{=Table(E63,E64)}

I take it the TABLE call in this example is E63 and E64? I am not
able to find the formula you referred to?

This is a tough one for me! I guess I need it explained in a fashion
for a true beginner!

Thank you for your time and assistance.
Mark
 
Hello, thanks for your insight, you obviously know spreadsheets very
well. I am still a novice and don't completely understand your
observation.

Would you mind expanding on your answer a bit further.

"The cells in the column immediately to the left of the array of cells
containing the TABLE call contain the formula(s) applied by TABLE to
the values in the row immediately above this array of cells."
{=Table(E63,E64)}

I take it the TABLE call in this example is E63 and E64? I am not
able to find the formula you referred to?

This is a tough one for me! I guess I need it explained in a fashion
for a true beginner!

Thank you for your time and assistance.
Mark
Click to expand...

Mark,

Based on what you have written and the exact format of the formula, it seems as
if you are looking at a regular worksheet (and not a macro sheet as I had
originally thought) in a release later than XL4.

If that is the case, look up DATA TABLE in HELP, and that should give you a
good start.


--ron
 
I don't know how the Data | Table capability is implemented (i.e.,
whether it uses an implicit XLM macro), but the =TABLE(...) array
function is a result of creating a one-way or a two-day table.

For more on the capability look up 'data table' (w/o the quotes) in XL
help. You'll find a bunch of topics such as 'About data tables' or
'Create a two-variable data table' or 'Create a one-variable data
table'

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
How does an "XLM Macro" function different from the XL4 Macro functions which
could be entered on a macro sheet, as I originally described?
Click to expand...

XLM and XL4 macro functions are the same thing. Back in the old days, macro
sheets were .XLM files, thus the reason I call them XLM functions. I'm not alone
in using this terminology.

http://www.google.com/[email protected]

That said, any built-in function that could be entered into cell formulas in an
XL2/3/4 .XLS file could also be entered into cell formulas in an XL2/3/4 .XLM
file, *but* macro functions could *only* be entered into .XLM file cell formulas
and never into .XLS cell formulas. It's possible TABLE is a partial exception.

However, since we're a couple of versions past Excel 5, it's a safer assumption
that the OP is looking at a worksheet rather than an XLM macro sheet.
TABLE is listed as such in the Function Reference for XL4 (remember when we
used to get printed manuals for MS products?).
Click to expand...

Yup, but it's listed as TABLE and TABLE?. I never used either in any XLM macros
I wrote, and I'm very rusty on XLM coding in general. However, I just created an
XLM macro sheet in XL8 and added the following macro, named foo.

=TABLE(,!A1)
=RETURN()

Then I switched to Sheet1 and created a dummy 1-way data table skeleton, with B1
containing the formula =10*A1 and A2:A6 containing {1;2;3;4;5}. I selected A1:B6
and ran foo, which created a data table out of Sheet1!A1:B6 with B2:B6 appearing
to contain the array formula =TABLE(,A1). So the TABLE function in XLM actually
adds a data table to the active worksheet (XL2/3/4 .XLS) which would appear as
=TABLE(.,.) in that worksheet.

I have no idea why TABLE isn't mentioned as a worksheet function in the old
manuals other than the possibility that Microsoft was embarrassed by using the
same token, TABLE, to represent different things in XLS and XLM worksheets. Or
they just don't give a damn about explaining to mere users/customers what their
software does.

Maybe the OP really is seeing this in an XL4 macro sheet, but I'd be willing to
bet s/he's seeing it in a regular worksheet as a data table. Wouldn't you?
 
Maybe the OP really is seeing this in an XL4 macro sheet, but I'd be willing to
bet s/he's seeing it in a regular worksheet as a data table. Wouldn't you?
Click to expand...

Well, I certainly would now, as his pasting of some more formulas suggests the
originals were on a worksheet and not a macro sheet.

Actually, I should have picked up on the absence of a ! and the presence of
braces {} on his first posting.

But I did recall using the TABLE function back in XL4 days, so that threw me
off, initially.


--ron
 
Back
Top