Values in negative

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 10:00 PM GMT


QUESTION: Hi Richard. How are you? Richard I am having problem with formulas column H =IF(G4="","",IF(G4<F4,F4-G4,IF(G4>F4,""))) and column I =IF(G4="","",IF(G4>F4,G4-F4,IF(G4<F4,"",))) when I manually enter the values in column F then both formula is working but when I used formula (to get the data from other sheet) in column F both formula is not working and values are in negative instead of blank. I am sure you can solve my problem. Thnaks in Advance.



I can't reproduce your problem--the formulas as you have given them to me work fine. I copied and pasted them to mysheet and had no problem with them working. Both when I manually entered data into column F and G, and when I brought the data over with a formula from another sheet.

The only piece of the puzzle that I don't have is what the formula is that brings the data over from the other sheet and how that data is formatted.

Otherwise I can't find an error in your formula.

---------- FOLLOW-UP ----------

QUESTION: Richard =VLOOKUP(B9,'OFS Store '!$C$2:H7315,6,FALSE)


I still can't reproduce your problem--I copied and pasted all of the formulas including the vlookup formula, and it all works okay for me. There may be something in the data or the formatting that is causing the problem. I have also seen situations where a formula just doesn't seem to work and I can't find the reason why. In those situations, sometimes when the data is copied to a new worksheet/workbook things work fine. I suggest you try this.

Create a new workbook and copy the entire sheets to the new workbook--first copy and paste only the values using paste special, and see if the formulas work, if they do, copy and paste the formats over and see if they still work.


---------- FOLLOW-UP ----------

QUESTION: Hi Richard I did the same thing even I migrate fromtobut same error, I got the problem but do not have solution see the below detail. We are using the formula in column H =IF(G2<F2,F2-G2,IF(G2>F2,"",)), it will work fine but if Column F value is in 2 digits and column G is 3 digits it will create problem If column G is more digits than column F (either 2 and 3 or 3 and 4) it will create problem.



Since I can't reproduce the results you are getting I suggest you rewrite the formula and use different logic, such as =IF(G2<F2,F2-G2,"") =IF(G2>F2,+G2-F2,"") Or something similar. There are several ways the formula can be written. Maybe this will get you different results.


©2021 eLuminary LLC. All rights reserved.