Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Morning all

    On my worksheet I have to columns one is on time and has the formula =IF(I11=<H11,"Yes",""), the other column is delayed and has the formula =IF(I11>H11,"No,"") both behave as expected and place a Yes or blank / No or blank as required.

    On my Pivot table I have selected Shipment type as the row and on time and delayed as the columns with the expectation of seeing the number of on times per shipment type and the number of delays by shipment type.

    The pivot seems to populate OK but is counting the results of Yes or blank to give 30,548 and the same in the delayed column.

    I have changed the field settings but I get either 0 or a #error, perhaps the problem lies in the way I am getting the Yes or No in the formulas on the Master sheet
    Attached Images Attached Images
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Instead of two columns, add only one column to the master sheet, with formula

    =IF(I11=<H11,"Yes","No")

    Add this column to the Column Area of the pivot table AND to the Data Area. You'll get columns for Yes and for No in the pivot table.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='795233' date='28-Sep-2009 07:25']Instead of two columns, add only one column to the master sheet, with formula

    =IF(I11=<H11,"Yes","No")

    Add this column to the Column Area of the pivot table AND to the Data Area. You'll get columns for Yes and for No in the pivot table.[/quote]
    Magic, thanks Hans

    Funnily enough I originally had it as one column, albeit with a formula not as elegant as your, =IF(I11=<H11,"Yes",IF(I11>H11,"No")) but I did not realise that in a pivot you could use the same data from a column twice and that is why I went over to 2 columns. Working great now though, thanks again
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •