How to perform a multi-field type of calculation on a table where 2 fields are variable

[AndrewVogel]

I have a data table that holds the data I need to forecast sales from a product over the next 10 years. The data includes 10 fields each with the number of products sold in each year. The data also includes a further 10 fields with the price per product in each of these 10 years (see illustrative structure below):

Product	Y1 sales	Y2 sales	Y3 sales	...	Y1 price	Y2 price	Y3 price	...
X	10	50	100		$1	$1.50	$2.00
Y	5	10	25		$10	$12	$12.50

What would be the easiest / most efficient way of performing the generic calculation:

Y[n] revenue = Y[n] sales * Y[n] price

Hence creating 10 new columns each with the corresponding years revenue? I would like to avoid having to use the formula tool and having a separate expression for each as the actual data set I am working with has a significantly larger number of fields. 

Any advice would be much appreciated

[mceleavey · Accepted solution]

Hi @AndrewVogel ,

I used the Transpose and Crosstab tools to create the calculations. This will be future-proofed against additional rows.

This is the workflow:

Which gives the following results:

Hope this helps.

M

[AndrewVogel]

Great! Thanks @mceleavey