SOLVED
Home

add criteria to the following formula

%3CLINGO-SUB%20id%3D%22lingo-sub-487901%22%20slang%3D%22en-US%22%3Eadd%20criteria%20to%20the%20following%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-487901%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20to%20add%20criteria%20to%20the%20following%20formula%20that%20says%20if%20cell%20BB30%20has%20any%20data%20then%20true%2C%20if%20not%20cell%20remains%20blank%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(%20IF(%24D%2420%26gt%3B0%2C%24AB30%26lt%3B%24AC30%2C1)%2C%20IF(%24F%2420%26gt%3B0%2C%24AD30%26lt%3B%24AE30%2C1)%2C%20IF(%24H%2420%26gt%3B0%2C%24AF30%26lt%3B%24AG30%2C1)%2C%20%24AH30%26lt%3B%24AI30%2C%20%24AJ30%26lt%3B%24AK30%2C%20%24AL30%26lt%3B%24AM30%2C%20%24AN30%26lt%3B%24AO30%2C%20%24AP30%26lt%3B%24AQ30%2C%20((%24D%2420%26gt%3B0)%2B(%24F%2420%26gt%3B0)%2B(%24H%2420%26gt%3B0))%20)%2C%20(%24Q30%2F%24P30)%2C%22%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-487901%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-487933%22%20slang%3D%22en-US%22%3ERe%3A%20add%20criteria%20to%20the%20following%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-487933%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F235152%22%20target%3D%22_blank%22%3E%40Greg%20Bonaparte%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Greg%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20formula%3C%2FP%3E%0A%3CPRE%3E%3DIF(%20AND(...)%2C%20(%24Q30%2F%24P30)%2C%20%22%22)%3C%2FPRE%3E%0A%3CP%3Eyou%20could%20modify%20as%3C%2FP%3E%0A%3CPRE%3E%3DIF(%20AND(...)*(LEN(BB30)%26gt%3B0)%2C%20(%24Q30%2F%24P30)%2C%20%22%22)%3C%2FPRE%3E%0A%3CP%3EIf%20any%20data%20it%20changes%20nothing%2C%20if%20not%20entire%20expression%20is%20FALSE%20and%20formula%20returns%20empty%20string%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Greg Bonaparte
Contributor

I would like to add criteria to the following formula that says if cell BB30 has any data then true, if not cell remains blank

 

=IF(AND( IF($D$20>0,$AB30<$AC30,1), IF($F$20>0,$AD30<$AE30,1), IF($H$20>0,$AF30<$AG30,1), $AH30<$AI30, $AJ30<$AK30, $AL30<$AM30, $AN30<$AO30, $AP30<$AQ30, (($D$20>0)+($F$20>0)+($H$20>0)) ), ($Q30/$P30),"")

1 Reply
Solution

@Greg Bonaparte ,

 

Hi Greg,

 

You formula

=IF( AND(...), ($Q30/$P30), "")

you could modify as

=IF( AND(...)*(LEN(BB30)>0), ($Q30/$P30), "")

If any data it changes nothing, if not entire expression is FALSE and formula returns empty string