IFS formula

%3CLINGO-SUB%20id%3D%22lingo-sub-3371914%22%20slang%3D%22en-US%22%3EIFS%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3371914%22%20slang%3D%22en-US%22%3E%3CP%3E%3DIFS(B5%26gt%3B%3D90%2C%22HP%22%2CB5%26gt%3B%3D70%22P%22%2CB5%26gt%3B%3D50%2C%22PP%22%2CB5%26lt%3B%3D49%2C%22MP%22%2CB5%3D%22na%22%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20use%20the%20above%20formula.%26nbsp%3B%20I%20am%20running%20into%20a%20problem%20when%20the%20cell%20has%20%22na%22.%26nbsp%3B%20The%20formula%20works%20great%20if%20there%20is%20a%20number%20in%20the%20cell%2C%20but%20I%20have%20to%20use%20%22na%22%20as%20a%20placeholder.%26nbsp%3B%20It%20returns%20a%20score%20when%20%22na%22%20is%20present%2C%20but%20I%20want%20it%20to%20be%20blank.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22cellison01_1-1652458675396.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371497i29AFA1B2829DED7A%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22cellison01_1-1652458675396.png%22%20alt%3D%22cellison01_1-1652458675396.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThis%20person%20doesn't%20have%20a%20score%20for%20this%20test%2C%20yet%20is%20considered%20Highly%20Proficient.%26nbsp%3B%20I%20need%20this%20to%20be%20blank.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3371914%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3371959%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3371959%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1389699%22%20target%3D%22_blank%22%3E%40cellison01%3C%2FA%3E%26nbsp%3BChange%20the%20formula%20to%3A%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DIFS(B3%3D%22na%22%2C%22%22%2CB3%26gt%3B%3D0.9%2C%22HP%22%2CB3%26gt%3B%3D0.7%2C%22P%22%2CB3%26gt%3B%3D0.5%2C%22PP%22%2CB3%26lt%3B%3D0.49%2C%22MP%22)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EFirst%20test%20for%20%22na%22%2C%20then%20for%20the%20values%2C%20noting%20that%2090%25%20%3D%200.9%20not%2090%2C%20etc%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

=IFS(B5>=90,"HP",B5>=70"P",B5>=50,"PP",B5<=49,"MP",B5="na","")

 

I am trying to use the above formula.  I am running into a problem when the cell has "na".  The formula works great if there is a number in the cell, but I have to use "na" as a placeholder.  It returns a score when "na" is present, but I want it to be blank.

 

cellison01_1-1652458675396.png

This person doesn't have a score for this test, yet is considered Highly Proficient.  I need this to be blank.

1 Reply

@cellison01 Change the formula to:

=IFS(B3="na","",B3>=0.9,"HP",B3>=0.7,"P",B3>=0.5,"PP",B3<=0.49,"MP")

 

First test for "na", then for the values, noting that 90% = 0.9 not 90, etc