Pivot Tables & Calculated Fields

[MarkAndrews]

Really simple one here

I know how to put calculated fields into pivot tables, however can you use formula’s in this function?

I have tried and failed to put an If statement into my calculated field & it wont accept?

Can anyone shed any light on this? thanks

 

[Andrew Poulsom]

What formula did you try?

 

[MarkAndrews]

=IF('No Of Orders*'=1,Sales/'No Of Orders*',"")

Thanks

 

[Andrew Poulsom]

For me that formula was accepted, but returned #VALUE! when No of Orders did not equal 1. This returned the Sales value or zero:

=IF('No Of Orders*'=1,Sales/'No Of Orders*',0)

My data fields were Sales and No Of Orders*, which I chose from the list in the Insert Calculated Field dialog box.

I am using Excel 2000.

 

[MarkAndrews]

For me that formula was accepted, but returned #VALUE! when No of Orders did not equal 1. This returned the Sales value or zero:

=IF('No Of Orders*'=1,Sales/'No Of Orders*',0)

My data fields were Sales and No Of Orders*, which I chose from the list in the Insert Calculated Field dialog box.

I am using Excel 2000.

Thanks Andrew, i'll revisit my pivot table & try again