Forum Discussion

ZRonTech's avatar
ZRonTech
Copper Contributor
Aug 18, 2021

Calculated fields in pivot

Good day,   Please assist, I have a formula for capping my calculation to 100%, as per the below   =IF(ISERROR(IF(AND(SUM(Actual/Target)>1,MaxInd<>0),1,SUM(Actual/Target))),0,IF(AND(SUM(Actual/Ta...
excel
HansVogelaar's avatar
HansVogelaar
MVP
Aug 18, 2021

ZRonTech 

Does this do what you want?

 

=IFERROR(IF(MaxInd<>0,IF(SUM(Actual/Target)>3,3,IF(SUM(Actual/Target)>1.5,1,5,SUM(Actual/Target)))),0)

  • ZRonTech's avatar
    ZRonTech
    Copper Contributor
    Aug 18, 2021

    HansVogelaar 

     

    Hi,

    Thanks for the above. This is working for the rows that have a MaxInd value.
    Where the MaxInd value is 'No', I'd like the actual result of Actual/Target

     

    How can I achieve this?

     

    Attahced are the results for what I desire to achieve (image one) and what the new formula currently outputs (two).

    So in essence, I'd like teh formula to display what is shown in red in the image one.

     

    Please assist,

     

    Thanks

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Aug 18, 2021

      ZRonTech 

      Did you try measure instead of calculated column?

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Aug 18, 2021

      ZRonTech 

      I'm afraid I don't really understand. Does this do what you want?

       

      =IFERROR(IF(MaxInd="",IF(SUM(Actual/Target)>3,3,IF(SUM(Actual/Target)>1.5,1,5,SUM(Actual/Target))),SUM(Actual/Target)),0)

      • ZRonTech's avatar
        ZRonTech
        Copper Contributor
        Aug 18, 2021

        HansVogelaar 

        Hi,

         

        Apologies for the confusion. The spreadsheet is also making use of a Calculated Item (image MaxInd). So, on the pivot, the data pulls as shown in Desired image.

         

        I would like the formula to work out the values in red (image Desired).

         

        Please assist

         

        Thanks

Resources