IFS Statement

%3CLINGO-SUB%20id%3D%22lingo-sub-1325087%22%20slang%3D%22en-US%22%3EIFS%20Statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1325087%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fforums%2Fpostpage%2Fboard-id%2FExcelGeneral%23%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3EFormulas%20and%20Functions%3C%2FA%3E%3C%2FP%3E%3CP%3EI%20have%20an%20IFS%20statement%20that%20I%20want%20to%20return%20a%20blank%20value%20if%20there%20is%20no%20data%20in%20the%20referenced%20cell.%3C%2FP%3E%3CP%3EIF%20G2%20is%20blank%20then%20the%20value%20the%20formula%20provides%20in%20O2%20should%20be%20blank%20as%20well.%26nbsp%3B%20I%20can't%20get%20this%20to%20happen%20as%20it%20is%20returning%20the%20first%20value%20in%20the%20statement.%26nbsp%3B%20I%20have%20tried%20putting%20an%20ISBLANK%20statement%20in%20the%20front%20and%20back%20of%20the%20formula%2C%20as%20well%20as%20using%20the%20%3D%22%22.%26nbsp%3B%20Where%20am%20I%20going%20wrong%3F%20Below%20formula%20works%20for%20every%20value%20indicated.%26nbsp%3B%20I%20just%20need%20the%20piece%20to%20return%20a%20blank%20cell%20if%20none%20of%20these%20are%20met.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFS(G2%26lt%3B10000%2C%22%240%20to%20%249%2C999%22%2CG2%26lt%3B15000%2C%22%2410%2C000%20to%20%2414%2C999%22%2CG2%26lt%3B20000%2C%22%2415%2C000%20to%20%2419%2C999%22%2CG2%26lt%3B30000%2C%22%2420%2C000%20to%20%2429%2C999%22%2CG2%26lt%3B50000%2C%22%2430000%20to%20%2449%2C999%22%2CG2%26lt%3B100000%2C%22%2450%2C000%20to%20%2499%2C999%22%2CTRUE%2C%22%24100%2C000%20and%20Greater%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1325087%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1325124%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20Statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1325124%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F630998%22%20target%3D%22_blank%22%3E%40Hanvey%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAdd%20this%20as%20your%20first%20test%20within%20the%20IFS%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EG2%3D%22%22%2C%22%22%0A%0Aso%20that%20it%20becomes%20like%20this%3A%0A%0A%3DIFS(G2%3D%22%22%2C%22%22%2CG2%26lt%3B10000%2C%22%240%20to%20%249%2C999%22%2CG2%26lt%3B15000%2C%22%2410%2C000%20to%20%2414%2C999%22%2CG2%26lt%3B20000%2C%22%2415%2C000%20to%20%2419%2C999%22%2CG2%26lt%3B30000%2C%22%2420%2C000%20to%20%2429%2C999%22%2CG2%26lt%3B50000%2C%22%2430000%20to%20%2449%2C999%22%2CG2%26lt%3B100000%2C%22%2450%2C000%20to%20%2499%2C999%22%2CTRUE%2C%22%24100%2C000%20and%20Greater%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Formulas and Functions

I have an IFS statement that I want to return a blank value if there is no data in the referenced cell.

IF G2 is blank then the value the formula provides in O2 should be blank as well.  I can't get this to happen as it is returning the first value in the statement.  I have tried putting an ISBLANK statement in the front and back of the formula, as well as using the ="".  Where am I going wrong? Below formula works for every value indicated.  I just need the piece to return a blank cell if none of these are met.

 

=IFS(G2<10000,"$0 to $9,999",G2<15000,"$10,000 to $14,999",G2<20000,"$15,000 to $19,999",G2<30000,"$20,000 to $29,999",G2<50000,"$30000 to $49,999",G2<100000,"$50,000 to $99,999",TRUE,"$100,000 and Greater")

2 Replies
Highlighted

@Hanvey 

Add this as your first test within the IFS:

 

G2="",""

so that it becomes like this:

=IFS(G2="","",G2<10000,"$0 to $9,999",G2<15000,"$10,000 to $14,999",G2<20000,"$15,000 to $19,999",G2<30000,"$20,000 to $29,999",G2<50000,"$30000 to $49,999",G2<100000,"$50,000 to $99,999",TRUE,"$100,000 and Greater")

 

 

Highlighted

@Riny_van_Eekelen 

Thank you! You indirectly resolved my issue!!!  Stupid mistake on my part.  While I have tried this in the past and it did not work, I copied and pasted the information into a different cell and discovered the issue.  I had previously set the column up with Data Validation and changed the way I was looking at the data later.  Doing this I had apparently not cleared out the validation and therefore the formula was not working as it should.  Problem resolved!