413272_en-US

liliconcom

applying formula to 2400 columns, but that each references a previous column's values?

I have a table of 2400 columns and 311 rows. Each column has values and no formula. However, I have a single formula I want to use on each column that references that column's values. For example, for column "80" I have next to it another column with this: 

Abs(
 Col Sum(
  (:Name( "80" ) / Col Sum( :Name( "80" ) )) *
  Ln( :Name( "80" ) / Col Sum( :Name( "80" ) ) )
 )
) / Ln( Col Number( :Name( "80" ) ) )

Ideally, I'd like a new table of columns that results from using this formula on each of the previous table's columns values. I can go through manually and type in the "80" after pasting each formula to a newly created column next to the original on the first table, but there must be a quicker way to do this? If not a completely new table, then can I somehow create a "lag" that simply references the column before to get the values rather than specifying a unique column name (e.g., "80") each time? But the first way would be preferable. Thank you.

Data TablemacOSScriptingRe: applying formula to 2400 columns, but that each references a previous column's values?

This works perfectly. Thank you.

Re: applying formula to 2400 columns, but that each references a previous column's values?

There are other ways to do this, but check out the recently-posted Scripting Tools Add-in, if I understand your request correctly you can select all of the columns you are interested in (start with a couple to make sure it works as expected) and enter this in the formula section:

 

Abs(
 Col Sum(
  (<COLUMN> / Col Sum( <COLUMN> )) *
  Ln( <COLUMN> / Col Sum( <COLUMN> ) )
 )
) / Ln( Col Number( <COLUMN> ) )</COLUMN></COLUMN></COLUMN></COLUMN></COLUMN>