write a formula

[former_member80767]

Hi，

I have to use custom Range Penetration，so I can't use the strandard guideline. So I wrote an if function that lists all the cases, just like this

if(customPIR<"20%"||customPM="S",8%,if("50%">customPIR<="20%"||customPM="S",6%,if("80%">customPIR<="50%"||customPM="S",5%,if("100%">customPIR<="80%"||customPM="S",4%,if("100%">customPIR||customPM="S",3%,if(customPIR<"20%"||customPM="A",7%,if("50%">customPIR<="20%"||customPM="A",5%,if("80%">customPIR<="50%"||customPM="A",4%,if("100%">customPIR<="80%"||customPM="A",3%,if("100%">customPIR||customPM="A",3%,if(customPIR<"20%"||customPM="B",5%,if("50%">customPIR<="20%"||customPM="B",4%,if("80%">customPIR<="50%"||customPM="B",3%,if("100%">customPIR<="80%"||customPM="B",2%,if("100%">customPIR||customPM="B",1%,if(customPIR<"20%"||customPM="C",4%,if("50%">customPIR<="20%"||customPM="C",3%,if("80%">customPIR<="50%"||customPM="C",2%,0%))))))))))))))))

I know there are problems with this formula,

How do I make the changes to make the formula work and express all the cases？

Thanks in advance and kind regards

[Former Member]

Hi

I would not use a giant IF statement for this. I am imagining that the output of this statement is a recommended increase percentage? While it is possible to fix this, maintaining it or adjusting the output year on year would be a nightmare. Instead, I would use a lookup table.

It appears that there are 5 "bands" of PIR - less than 20%, between 20 and 50%, between 50 and 80%, between 80 and 100% and greater than 100%. There are four PM ratings - S, A, B and C.

The first task is to create a column that takes customPIR and converts it into a band. Let's be simple and call them band 1 through 5. Create a custom column of type string with the ID customBand and use this formula:

if(customPIR<0.2,"1",if(customPIR<0.5,"2",if(customPIR<0.8,"3",if(customPIR<1,"4","5"))))

Then, make a lookup table (let's call it "2021_Recommendations") with two inputs and one output:

then, replace your function with a simple lookup (I am assuming your custom column is of type Percentage):

toNumber(lookup("2021_Recommendations",customBand,customPM,1))/100

This solution allows for the year on year changes to be updated easily in the lookup table.

I hope this helps!

Phil