Multiple Field Formula
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello,
I am trying to use the Multiple Field Formula tool in order to add/subtract values in 3 rows together. I am working on an apportionment workflow where my first row is my gross unadjusted data and the two subsequent rows are adjustments that need to be added to the first row. How do I create my formula in the expression tool to get my desired result?
Solved! Go to Solution.
- Labels:
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Since you are effectively adding all the rows together, wouldn't the Summarize tool suffice here?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @ebledsoe22
You ask is possible by Multi row formula tool.
However can you post sample input and output expected so relevant formula can be suggested to get the desired result.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @ebledsoe22,
It sounds to me like you need to use the multi-ROW formula tool instead of the multi-FIELD. You can reference rows above and below using this tool. If you need some further help please attach some sample data and I'll help you write the formula.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @BS_THE_ANALYST I only need to apply the formula to 3 out 14 rows, not the entire data set.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You could think about using the sample tool to take only the first 3 rows, then either use a multi-row formula or a summarize tool like @BS_THE_ANALYST has suggested. You might also be able to CrossTab your data to turn the rows to columns, and then use a normal formula tool. Again some sample data would help.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @ShankerV ad @FinnCharlton, I have 14 rows, but almost 60 columns due to this being a state apportionment project. Across the top are my states, and then my rows are labeled according to the different factors. I need this formula applied to all of the state columns, but only on 3 rows. Row 1 is the base inventory number, wile rows 2 and 3 are adjustments. I want to add the adjustments to the gross value for each state.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@ebledsoe22 okay, we can add a record ID to the data. We can then filter out the records you want to select using the Filter tool. We can then apply the Summarize tool. See attached mock data ..
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Alternatively, you can use the Select Records tool which gives more intuitive flexibility to select the records you want to sum together. See attached
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @ebledsoe22
As you want to apply the sum for all 60 columns, multi field formula can't help.
You need to filter all the first 3 rows.
Do the Cross tab tool to do in all on row.
Then apply the multi row formula and then Transpose back.
Join to the original dataset.
Many thanks
Shanker V
![](/skins/images/1AD6617C767659D8A9F9801BEDFCFB20/responsive_peak/images/icon_anonymous_message.png)