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

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

fever — Sat, 10 Jun 2023 23:36:17 GMT

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.

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

ih — Fri, 27 Aug 2021 19:42:10 GMT

There are other ways to do this, but check out the recently-posted 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> ) )

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

fever — Sat, 28 Aug 2021 15:42:59 GMT

This works perfectly. Thank you.