- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
use a value to find the correct column to use in a formula
Hi,
I have a summary table where I'd like to create a new column 'Rate' with a formula. The column MAXRESULT gives the name of the column I want to use in the formula. unfortunately, I'm getting 'missing arguments' error when I create the formula based on column name.
Column 'Rate' formula:
(100 * Column Name( Parse( "N(" || Char( :MAXRESULT ) || ")" ) )[]) / :N Rows
What's wrong ?
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: use a value to find the correct column to use in a formula
One option is to use As Column() or Column() instead of Column Name() (with As Column() you shouldn't need []). Also there should be no need to use Parse().
Something like this
(100 * As Column("N(" || Char(:MAXRESULT) || ")")) / :N Rows
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: use a value to find the correct column to use in a formula
Here is an example that shows that @jthi column does work. I believe that you have an issue with your data.
New Table( "Untitled 12",
Add Rows( 4 ),
New Column( "N(1.6)",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [445, 446, 0, 0] )
),
New Column( "N(1.5)",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [0, 0, 441, 446] )
),
New Column( "MAXRESULT",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [1.6, 1.6, 1.5, 1.5] )
),
New Column( "N Rows",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [446, 446, 446, 446] )
),
New Column( "Rate",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula( (100 * As Column( "N(" || Char( :MAXRESULT ) || ")" )) / :N Rows ),
Set Selected
)
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: use a value to find the correct column to use in a formula
One option is to use As Column() or Column() instead of Column Name() (with As Column() you shouldn't need []). Also there should be no need to use Parse().
Something like this
(100 * As Column("N(" || Char(:MAXRESULT) || ")")) / :N Rows
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: use a value to find the correct column to use in a formula
Hi,
the proposed formula still doesn't work
Error unresolved column
Error Could not find column
anything else I can try out ?
Thanks for your support
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: use a value to find the correct column to use in a formula
Here is an example that shows that @jthi column does work. I believe that you have an issue with your data.
New Table( "Untitled 12",
Add Rows( 4 ),
New Column( "N(1.6)",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [445, 446, 0, 0] )
),
New Column( "N(1.5)",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [0, 0, 441, 446] )
),
New Column( "MAXRESULT",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [1.6, 1.6, 1.5, 1.5] )
),
New Column( "N Rows",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [446, 446, 446, 446] )
),
New Column( "Rate",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula( (100 * As Column( "N(" || Char( :MAXRESULT ) || ")" )) / :N Rows ),
Set Selected
)
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: use a value to find the correct column to use in a formula
ok it's working on my data set as well.
the strange thing is it's still showing the error in the preview but then when I apply the formula, the result is good.
Thank you all !
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: use a value to find the correct column to use in a formula
Formula editor doesn't like the formula. There are quite a few formulas which can have those red-underlines but they will still work (using Current Data Table() for example). In this case I think it is caused by not evaluation the column names into the formula