Hi all,
I'm currently facing this issue.
I need to do the calculation like this: [difference]=[sum1]-[sum2], and [sum1] and [sum2] would always be the 9th and the 10th row. it would be simple with formula when the data is shown in column, how can data be subtracted between two rows?
The sample data is as shown below. Only focusing on highlighted ones would be enough. Thank you.
Hi @Paddi
Please use the multi-row formula, but you need to do it repeatedly for many rows.
But however you can easily achieved is using Multi-Field formula.
Many thanks
Shanker V
Hey @Paddi, here's one way you could go about this:
You mention that sum1 and sum2 are always in certain rows and we can therefore then isolate them with Select Records:
From there, we can then Transpose and re-Cross Tab our data to bring the two sums onto a single row so that we can calculate the difference:
From here, we then just Cross-Tab the data again to get back to our original headers, ready for them to slot in nicely when we Union this new calculated row to the bottom of the existing data set:
The final Select and Formula tools here are just making the F1 field larger in size so that we can then populate it with 'Difference' instead of leaving it null:
Hope this helps - please shout up if you need any further help!
Thank you all! @Felipe_Ribeir0 @binuacs @DataNath @ShankerV
Really excellent and useful solutions!!! It's amazing to see that the issue can be solved in so many different ways. Thanks again.