Comparison filters effecting sum

[Anonymous]

Please can anyone help me with the following formula. 

P- = 1

P   =2

P+ =3

Pr2-Pr1 = (KS3[Prog Y8Pr2])-(KS3[Prog Y8Pr1])

I am trying to make this sum work on a page when you select any filters/slices. however the sum is taking away the PR2 numbers even though they are not dispalying in the visual making the progress numbers and arrows wrong. 

Many thanks

[Anonymous]

The filters are filtering columns with the letter P- , P, P+. and the PR1-Pr2 difference collumn is using a helper column I created using the numbers for the  P-=1 P=2 P+ = 3 for each series PR1 and PR2.

IF(AND(KS3[Resultset]="Yr 8 PR1",KS3[P Helper]="P"),"2",IF(AND(KS3[Resultset]="Yr 8 PR1",KS3[P Helper]="P-"),"1",IF(AND(KS3[Resultset]="Yr 8 PR1",KS3[P Helper]="P+"),"3",IF(AND(KS3[Resultset]="Yr 8 PR1",KS3[P Helper]=""),"0"))))

What i think I need to create is some kind of formula using your if blank and something like this to convert the P-,P,P+ without the helper column?

IF(AND(KS3[Resultset]="Yr 8 PR1",KS3[P Helper]="P"),"2",IF(AND(KS3[Resultset]="Yr 8 PR1",KS3[P Helper]="P-"),"1",IF(AND(KS3[Resultset]="Yr 8 PR1",KS3[P Helper]="P+"),"3",IF(AND(KS3[Resultset]="Yr 8 PR1",KS3[P Helper]=""),"0"))))                minus 

IF(AND(KS3[Resultset]="Yr 8 PR2",KS3[P Helper]="P"),"2",IF(AND(KS3[Resultset]="Yr 8 PR2",KS3[P Helper]="P-"),"1",IF(AND(KS3[Resultset]="Yr 8 PR2",KS3[P Helper]="P+"),"3",IF(AND(KS3[Resultset]="Yr 8 PR2",KS3[P Helper]=""),"0"))))   however i just cant work out the correct formula. 😞

[joaoribeiro]

Hi @Anonymous ,

To avoid making the calculation when any column is blank, you can use the IF statement below to check if there are blanks before the sum. If there is any blank, them the calculation will return BLANK (or you can assign a specific value to these cases):

Pr2-Pr1 =
IF(
ISBLANK(KS3[Prof Y8Pr1]) || ISBLANK(KS3[Prof Y8Pr2]),
BLANK(),
KS3[Prof Y8Pr2] - KS3[Prof Y8Pr1]
)

This formula basically check if Pr1 OR Pr2 are blanks, if any of them is blank, them it returns blank, otherwise it returns the Pr2-Pr1.

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️or giving it a kudoe 👍

Thanks!
Best regards,
Joao Ribeiro