Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy a calculated field
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy a calculated field
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. -- Thanks, Kevin "Gino59" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy a calculated field
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? "AFSSkier" wrote: 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. -- Thanks, Kevin "Gino59" wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy a calculated field
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. -- "Gino59" wrote: 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? "AFSSkier" wrote: 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. -- Thanks, Kevin "Gino59" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want to create a calculated item based on a calculated field | Excel Discussion (Misc queries) | |||
Calculated Field and Calculated Item in Pivot Table | Excel Discussion (Misc queries) | |||
PivotTable:Using a calculated field result in another calculated f | Excel Worksheet Functions | |||
pivot table formulas for calculated field or calculated item | Excel Discussion (Misc queries) | |||
how to create a calculated field from another calculated field? | Excel Discussion (Misc queries) |