|
pivot table calculated field or item
Hi, my question is..I want to use an If formula based on the Grp colomn value
BU (see formula below), but it doesnt recognise the value as "BU", it just returns the false value (value if false). Pivot table rows are as follows SO # Grp Cust Whse SO 311450 BQ BAKERS S 10-Nov-Tue 312385 0 MODERN S 12-Nov-Thu 312403 BU BUNNINGS S 12-Nov-Thu I have inserted a calculated field with formula =IF(Grp="BU",0,1) The formula always returns 1 Thanks for your answer |
pivot table calculated field or item
Hi Craig
Add an extra column to your source data headed Test with a formula like =IF(B2="BU",0,1) Then expand your source to include the new column. Drag Test to the area where you want it on the PT - presumably the Data area -- Regards Roger Govier "craig" wrote in message ... Hi, my question is..I want to use an If formula based on the Grp colomn value BU (see formula below), but it doesnt recognise the value as "BU", it just returns the false value (value if false). Pivot table rows are as follows SO # Grp Cust Whse SO 311450 BQ BAKERS S 10-Nov-Tue 312385 0 MODERN S 12-Nov-Thu 312403 BU BUNNINGS S 12-Nov-Thu I have inserted a calculated field with formula =IF(Grp="BU",0,1) The formula always returns 1 Thanks for your answer __________ Information from ESET Smart Security, version of virus signature database 4817 (20100129) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4817 (20100129) __________ The message was checked by ESET Smart Security. http://www.eset.com |
pivot table calculated field or item
Hi Roger, Thanks but I didnt reallly want to have to modify my source data.
Is there a reason the pivot table formula wont recognise the text criteria? Is this a limitation of pivot tables? The formula is simple enough, I cant understand why it wont work. "Roger Govier" wrote: Hi Craig Add an extra column to your source data headed Test with a formula like =IF(B2="BU",0,1) Then expand your source to include the new column. Drag Test to the area where you want it on the PT - presumably the Data area -- Regards Roger Govier "craig" wrote in message ... Hi, my question is..I want to use an If formula based on the Grp colomn value BU (see formula below), but it doesnt recognise the value as "BU", it just returns the false value (value if false). Pivot table rows are as follows SO # Grp Cust Whse SO 311450 BQ BAKERS S 10-Nov-Tue 312385 0 MODERN S 12-Nov-Thu 312403 BU BUNNINGS S 12-Nov-Thu I have inserted a calculated field with formula =IF(Grp="BU",0,1) The formula always returns 1 Thanks for your answer __________ Information from ESET Smart Security, version of virus signature database 4817 (20100129) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4817 (20100129) __________ The message was checked by ESET Smart Security. http://www.eset.com |
pivot table calculated field or item
Hi Craig
If you want to send me a sample of your raw data, then I will see if I can come up with any other solution. To mail direct roger at technology4u dot co dot uk Change the at and dots to make a valid email address. -- Regards Roger Govier "craig" wrote in message ... Hi Roger, Thanks but I didnt reallly want to have to modify my source data. Is there a reason the pivot table formula wont recognise the text criteria? Is this a limitation of pivot tables? The formula is simple enough, I cant understand why it wont work. "Roger Govier" wrote: Hi Craig Add an extra column to your source data headed Test with a formula like =IF(B2="BU",0,1) Then expand your source to include the new column. Drag Test to the area where you want it on the PT - presumably the Data area -- Regards Roger Govier "craig" wrote in message ... Hi, my question is..I want to use an If formula based on the Grp colomn value BU (see formula below), but it doesnt recognise the value as "BU", it just returns the false value (value if false). Pivot table rows are as follows SO # Grp Cust Whse SO 311450 BQ BAKERS S 10-Nov-Tue 312385 0 MODERN S 12-Nov-Thu 312403 BU BUNNINGS S 12-Nov-Thu I have inserted a calculated field with formula =IF(Grp="BU",0,1) The formula always returns 1 Thanks for your answer __________ Information from ESET Smart Security, version of virus signature database 4817 (20100129) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4817 (20100129) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4822 (20100131) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4822 (20100131) __________ The message was checked by ESET Smart Security. http://www.eset.com |
pivot table calculated field or item
Thanks Roger but I have used your suggestion (sort of) by using a different
field from my data for the if logic test criteria. In summary instead of using a text field I am using a numeric value field which works fine. It appears to me that formulas in pivot tables do not like text values as criteria in the row fields. Thanks for your help "Roger Govier" wrote: Hi Craig If you want to send me a sample of your raw data, then I will see if I can come up with any other solution. To mail direct roger at technology4u dot co dot uk Change the at and dots to make a valid email address. -- Regards Roger Govier "craig" wrote in message ... Hi Roger, Thanks but I didnt reallly want to have to modify my source data. Is there a reason the pivot table formula wont recognise the text criteria? Is this a limitation of pivot tables? The formula is simple enough, I cant understand why it wont work. "Roger Govier" wrote: Hi Craig Add an extra column to your source data headed Test with a formula like =IF(B2="BU",0,1) Then expand your source to include the new column. Drag Test to the area where you want it on the PT - presumably the Data area -- Regards Roger Govier "craig" wrote in message ... Hi, my question is..I want to use an If formula based on the Grp colomn value BU (see formula below), but it doesnt recognise the value as "BU", it just returns the false value (value if false). Pivot table rows are as follows SO # Grp Cust Whse SO 311450 BQ BAKERS S 10-Nov-Tue 312385 0 MODERN S 12-Nov-Thu 312403 BU BUNNINGS S 12-Nov-Thu I have inserted a calculated field with formula =IF(Grp="BU",0,1) The formula always returns 1 Thanks for your answer __________ Information from ESET Smart Security, version of virus signature database 4817 (20100129) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4817 (20100129) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4822 (20100131) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4822 (20100131) __________ The message was checked by ESET Smart Security. http://www.eset.com |
| All times are GMT +1. The time now is 02:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com