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/Target)>1,MaxInd<>0),1,SUM(Actual/Target)))

 

So, the above formula returns 100% if Actual/Target > 100%

 

How can I update the above formula to apply 2 cappings, for 150% and 300%?

 

I want the new formula to return 150% if Actual/Target > 150%, and o return 300%% if Actual/Target > 300%

 

Please advise

 

Mnay thanks

 

excel

7 Replies

  • 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

Resources