Help With Reducing Negative Value

%3CLINGO-SUB%20id%3D%22lingo-sub-2023737%22%20slang%3D%22en-US%22%3EHelp%20With%20Reducing%20Negative%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2023737%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20reduce%20the%20amount%20of%20a%20negative%20value%20representing%20a%20loan%20payment%20in%20Excel.%26nbsp%3B%20I%20am%20transferring%20the%20principal%20payment%20to%20reduce%20the%20negative%20amount%20and%20have%20tried%20various%20versions%20to%20try%20to%20accomplish%20this.%26nbsp%3B%20However%2C%20it%20always%20increases%20the%20ending%20balance%20on%20the%20worksheet.%20I%20had%20a%20chat%20session%20with%20Microsoft%20support%20for%20over%20an%20hour%20today%20and%20they%20couldn't%20figure%20out%20how%20to%20accomplish%20this%20either.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20copy%20of%20a%20worksheet%20with%20the%20cells%20highlighted%20that%20I%20want%20to%20change.%26nbsp%3B%20The%20table%20on%20the%20left%20is%20the%20original%20and%20the%20one%20one%20the%20right%20reflects%20the%20changes%20I%20made.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20assistance%20would%20be%20greatly%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2023737%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2023754%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20With%20Reducing%20Negative%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2023754%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F914704%22%20target%3D%22_blank%22%3E%40ShellB380%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESeems%20like%20a%20simple%20IF%20statement%20will%20do%20the%20trick.%20I'm%20a%20bit%20confused%20by%20how%20the%20yellow%20cells%20are%20connected%20but%20this%20would%20be%20how%20the%20IF%20statement%20will%20work%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(C27%26gt%3B-9000%2C%20X%2C%20Y)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EX%20is%20the%20TRUE%20scenario%20and%20that's%20where%20you%20can%20input%20a%20default%20value%20if%20negative%20value%20is%20too%20high.%3C%2FP%3E%3CP%3EY%20is%20the%20FALSE%20scenario%20and%20can%20replaced%20by%20the%20regular%20function%20that%20would%20be%20used%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2023790%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20With%20Reducing%20Negative%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2023790%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F867265%22%20target%3D%22_blank%22%3E%40adversi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20you%20quick%20response.%26nbsp%3B%20I%20have%20little%20expertise%20with%20Excel%20formulas%2C%20so%20here's%20what%20the%20highlighted%20cells%20show%3A%3C%2FP%3E%3COL%3E%3CLI%3ECell%20K20%20is%20the%20loan%20interest%20payment%26nbsp%3B%3C%2FLI%3E%3CLI%3ECell%20J20%20is%20the%20loan%20principal%20payment%2C%20which%20I%20want%20to%20transfer%20to%20K27%20to%20reduce%20the%20negative%20amount.%3C%2FLI%3E%3CLI%3ECell%20J27%20is%20the%20original%20loan%20amount.%3C%2FLI%3E%3CLI%3ECell%20E45%20is%20the%20correct%20balance%20amount%3B%20L45%20is%20the%20wrong%20amount.%3C%2FLI%3E%3C%2FOL%3E%3CP%3EI%20tried%20using%20your%20IF%20Statement%2C%20however%2C%20the%20balance%20still%20increased.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2023809%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20With%20Reducing%20Negative%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2023809%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F914704%22%20target%3D%22_blank%22%3E%40ShellB380%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStill%20a%20bit%20confused.%20If%20I%20understand%20correctly%2C%20the%20two%20differences%20between%20E45%20and%20L45%20is%3A%3C%2FP%3E%3CP%3E1.%20the%20exclusion%20of%20the%20-100%20(J20%20that%20is%20referenced%20in%20K27)%3B%20and%3C%2FP%3E%3CP%3E2.%20K20%20being%204.91%20instead%20of%20104.91%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBefore%20setting%20up%20the%20IF%20function.%20what%20is%20the%20rule%20you%20are%20trying%20to%20establish%3F%20Is%20the%20figure%20supposed%20to%20be%20split%20or%20reduced%20by%20a%20certain%20amount%3F%20Is%20the%20104.91%20a%20static%20figure%20or%20something%20that%20is%20generated%20through%20a%20calculation%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
I'm trying to reduce the amount of a negative value representing a loan payment in Excel.  I am transferring the principal payment to reduce the negative amount and have tried various versions to try to accomplish this.  However, it always increases the ending balance on the worksheet. I had a chat session with Microsoft support for over an hour today and they couldn't figure out how to accomplish this either.
 
Any assistance would be greatly appreciated!
3 Replies

@ShellB380 

Seems like a simple IF statement will do the trick. I'm a bit confused by how the yellow cells are connected but this would be how the IF statement will work:

=IF(C27>-9000, X, Y)

 

X is the TRUE scenario and that's where you can input a default value if negative value is too high.

Y is the FALSE scenario and can replaced by the regular function that would be used

 

 

@adversi 

Thank you for you quick response.  I have little expertise with Excel formulas, so here's what the highlighted cells show:

  1. Cell K20 is the loan interest payment 
  2. Cell J20 is the loan principal payment, which I want to transfer to K27 to reduce the negative amount.
  3. Cell J27 is the original loan amount.
  4. Cell E45 is the correct balance amount; L45 is the wrong amount.

I tried using your IF Statement, however, the balance still increased.

@ShellB380 

Still a bit confused. If I understand correctly, the two differences between E45 and L45 is:

1. the exclusion of the -100 (J20 that is referenced in K27); and

2. K20 being 4.91 instead of 104.91

 

Before setting up the IF function. what is the rule you are trying to establish? Is the figure supposed to be split or reduced by a certain amount? Is the 104.91 a static figure or something that is generated through a calculation?