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
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

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