How to multiply 2 columns with variable range?

[Wino]

I have 1 pivottable with column1 and column2 both with variable ranges. I would like to multiply both and see the result in column3 next to the pivottable.

To give an idea:

Cell 1 of column1 * cell 1 of column2 = cell 1 of column3
Cell 2 of column1 * cell 2 of column2 = cell 2 of column3
...
lastrow of column1 * lastrow of column2 = lastrow of column3

The ranges are defined (lastrow is also known), but I dont succeed in multiplying.

Column1 has range "myrange1" and column2 has range "myrange2".

Any help would be greatly appreciated!

[Palmetto]

From the way you have described your situation, you will need to create a Calculated Field for your Pivot Table that multiplies the two applicable fields in the Pivot Table List.

See attached.

To add a Calculated Field to the PT.

1. Activate the PT
2. Choose Calculated Field from the PT tool bar - dialog box will appear
3. Give the Field a meaningful name in the Name field
4. Click into the Formula and delete the zero
5. In the list of fields below, double click to add a field - do this for each of the two fields you require
6. Click back into the Formula field and type a * (asterisk) character between the fields.
7. Click OK to add the field to the PT.

[Wino]

Thanks for your reply. The calculated field is a very promising feature.

I now realise that column2 is not in the pivottable but generated next to the pivottable. Can I still use the calculated field?

I fail in trying that.

The position of column2 relative to the last column of the pivot is identified. Can I use calculated field with columns outside the pivot?

Thx in advance! In the meantime I keep trying...

[Palmetto]

You will get a more accurate answer if you upload an example workbook and manually hsow the expected results you want to achieve.

I now realise that column2 is not in the pivottable but generated next to the pivottable. Can I still use the calculated field?

I wouldn't depend on such an approach because PT are subject to be changed. IMO, only items in the PT field list should be used in creating calculated fields.