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 

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

    • ZRonTech's avatar
      ZRonTech
      Copper Contributor
      Aug 19, 2021
      Hi,

      I unfortunately do not have rights to share the spreadsheet

      Many thanks

Resources