Copy a calculated field


G

Gino59

I entered a calculated field but when I try to copy and paste the formula
down the column of over 13,000 entries, it keeps the exact same formula that
was entered in the first calculation. I'd like it to look at the row
information and use that as well.

=GETPIVOTDATA("Student Id2",$A$3,"ComputePass","Passed","Test Date
Month",DATE(1904,1,15),"Iot","Asia Pacific","Mappedsponsor","IBM
Cognos","Test Series","BI0-112")/GETPIVOTDATA("Student Id2",$A$3,"Test Date
Month",DATE(1904,1,15),"Iot","Asia Pacific","Mappedsponsor","IBM
Cognos","Test Series","BI0-112")

What changes are the "Iot", "MappedSponsor", "Test Series" fields. For
example, the first 400 rows list are for a sponsor called IBM Cognos which is
then followed by the Geography (IOT) - say about 124 rows. Then this is
further refined by the unique test id. Each row has a summary column for
tests passed, tests failed, and total tests. The idea is to have a
calculated field after total tests which calculates the unique passing
percentage.

How can I do this without having to individually enter in all 13K+
"getpivotdata" calculations?

Thanks!
 
Ad

Advertisements

A

AFSSkier

Gino,

From the "GETPIVOTDATA", it appears you're trying to do calculations on a
pivot table. Pivot table calcs should be done with the pivot table formula
wizard (PivotTable Tools, Options, Formulas, Calculated Field). The formula
will copy it's self down. Also as the pivot table grows & shrinks, so will
your formula cells.

If you still want to do a calc outside the pivot table, enter the cell
reference manully (B5*C6). Than you will be able to copy the formula down.
 
G

Gino59

Thanks, Kevin - that's exactly what I'm doing (a pivot table calc). Works
like a charm in Excel 2007. I have a customer who can't use the pivot table
I created because he's on '03 Excel (i saved the file as that). So now I'm
trying to re-create the pivot in '03 Excel and that's where I'm running into
a problem. It just doesn't work the same and I'm probably in the wrong blog
but thought I'd ask.

Either that or is there a way to get Excel '07 pivot tables to work in Excel
'03?
 
Ad

Advertisements

A

AFSSkier

You should be able to create your pivot tables in 2007, than save as 2003. I
do it all the time, without any problems. The only problem you should run
into is styling. But even at that, 2007 does a great job at converting.
--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top