If Then and Formula assistance

%3CLINGO-SUB%20id%3D%22lingo-sub-2225453%22%20slang%3D%22en-US%22%3EIf%20Then%20and%20Formula%20assistance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2225453%22%20slang%3D%22en-US%22%3E%3CP%3EGreetings%2C%3C%2FP%3E%3CP%3EI%20am%20having%20trouble%20scripting%20the%20correct%20command%20and%20am%20hoping%20that%20there%20is%20a%20way%20to%20do%20it%20and%20that%20some%20one%20can%20help%20me.%26nbsp%3B%20I%20am%20using%20PC%20with%20Office%20365.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20of%20now%2C%20N4%20equals%20L4%20-%20M4.%26nbsp%3B%20I%20would%20like%20to%20maintain%20that%20formula%20if%20the%20value%20in%20K4%20is%20positive.%26nbsp%3B%20If%20the%20value%20in%20K4%20is%20negative%2C%20then%20I%20would%20like%20the%20formula%20used%20to%20be%20N4%20equals%20L4.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20time%20and%20any%20assistance%20you%20may%20provide!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2225453%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-2225485%22%20slang%3D%22en-US%22%3ERe%3A%20If%20Then%20and%20Formula%20assistance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2225485%22%20slang%3D%22en-US%22%3ESeems%20pretty%20straight%20forward%20to%20me%2C%20if%20I%20understand%20your%20question%20correctly%20of%20course.%3CBR%20%2F%3E%3CBR%20%2F%3Euse%20the%20conditional%20IF%20operator%20to%20apply%20the%20result%3CBR%20%2F%3Einto%20N4%20you%20would%20use%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIF(K4%26gt%3B0%2CL4-M4%2CL4)%3CBR%20%2F%3E%3CBR%20%2F%3Esince%20your%20condition%20of%20%22is%20positive%22%20is%20binary%20(yes%20or%20no)%20you%20only%20need%20one%20logical%20test%20because%20the%20other%20condition%20is%20therefore%20implied%3CBR%20%2F%3E%3CBR%20%2F%3E*this%20only%20holds%20true%20if%20there%20are%20only%20positive%20or%20negative%20numbers%2C%200%20is%20neither%20so%20I%20don't%20know%20if%20you%20need%20to%20handle%20that%20case%3C%2FLINGO-BODY%3E
New Contributor

Greetings,

I am having trouble scripting the correct command and am hoping that there is a way to do it and that some one can help me.  I am using PC with Office 365.  

 

As of now, N4 equals L4 - M4.  I would like to maintain that formula if the value in K4 is positive.  If the value in K4 is negative, then I would like the formula used to be N4 equals L4.

 

Thank you for your time and any assistance you may provide!

 

 

 

4 Replies
Seems pretty straight forward to me, if I understand your question correctly of course.

use the conditional IF operator to apply the result
into N4 you would use:

=IF(K4>0,L4-M4,L4)

since your condition of "is positive" is binary (yes or no) you only need one logical test because the other condition is therefore implied

*this only holds true if there are only positive or negative numbers, 0 is neither so I don't know if you need to handle that case

@TRethan009 

As variant that could be

=L4-M4*(K4>0)

And as a comment I'd remove wrapping by IFERROR() every formula in the sheet, only where necessary.  In current variant that's mainly since you use =IFERROR(D3/F3,"") returning empty string in case of error. Thus we have column with combination of different value types in it, numbers and texts. Thus we have to handle that with all other formulas to check do we have with text of with number.

I'd return number in case of error (zero) as =IFERROR(D3/F3,0), thus we may simplify all next formulas.

Not to show zero we may hide it by format, e.g. just add another semicolon at the end of existing format string in column N

"$"#,##0.00_);[Red]("$"#,##0.00);

More exactly, it shall be blank between second and third parts of the format string

Exactly what I was looking for, didn't know how to write it. Thank you so much for your help I really appreciate it!
Thank you for taking the time to reply and offering a solution, I appreciate the info, thank you!