|
Negative numbers in an IF formula
I'm working on an IF formula:
=IF(D9="","",IF(AND(G90,G100),"",IF(G9=G10,G9,G 10))+IF(E10=0,"",E10)) G9 and G10 can be positive or negative. If G9 or G10 is a negative number, I want the greater of the two to be used and then to be added to E10. But right now, Excel is saying -20 (which is G10) is greater than zero. I'm getting a VALUE error. Thanks. TammyS |
Negative numbers in an IF formula
You are getting a #value because of the E10 comment. Change it to IF(E10=0,0,E10)) Another thing is you might want to use large instead of the the IF command, I think it's cleaner: Personally, you don't even need the whole E10 thing.. =IF(D9="","",IF(AND(G90,G100),"",LARGE(G9:G10,1) +E10)) It's cleaner this way.. you will STILL get a #Value if anything other than number is in E10.. You can protect against that with: =IF(D9="","",IF(AND(G90,G100),"",LARGE(G9:G10,1) +IF(ISNUMBER(E10),E10,0))) -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=563067 |
Negative numbers in an IF formula
Try
=IF(D9="","",IF(AND(G90,G100),"",IF(G9=G10,G9,G 10)+IF(E10=0,0,E10))) I think your #VALUE error arose because you were sometimes trying to add "" plus a number. Hope this helps, Hutch "TammyS" wrote: I'm working on an IF formula: =IF(D9="","",IF(AND(G90,G100),"",IF(G9=G10,G9,G 10))+IF(E10=0,"",E10)) G9 and G10 can be positive or negative. If G9 or G10 is a negative number, I want the greater of the two to be used and then to be added to E10. But right now, Excel is saying -20 (which is G10) is greater than zero. I'm getting a VALUE error. Thanks. TammyS |
Negative numbers in an IF formula
First of all, the formula will result in an error whenever any of the IF
condition evaluates to "", because addition (+) doesn't work with "" (empty string). So basically there are 4 possible final results from the formula: "" + "" = error number + "" = error "" + number = error number + number = number Do you actually just want a "" as a final result instead of a partal result? If so, try: =IF(D9="","",IF(E10=0,"",IF(AND(G90,G100),"",IF( G9=G10,G9+E10,G10+E10)))) Secondly, what tells you that Excel is saying G10 (-20) is greater than zero? Sorry I couldn't figure that part out. "TammyS" wrote: I'm working on an IF formula: =IF(D9="","",IF(AND(G90,G100),"",IF(G9=G10,G9,G 10))+IF(E10=0,"",E10)) G9 and G10 can be positive or negative. If G9 or G10 is a negative number, I want the greater of the two to be used and then to be added to E10. But right now, Excel is saying -20 (which is G10) is greater than zero. I'm getting a VALUE error. Thanks. TammyS |
Negative numbers in an IF formula
Thanks. That got rid of the #VALUE error. But how do I get it to only use
the negative numbers? If there's a positive number, I want the cell (E13) to remain blank. "Tom Hutchins" wrote: Try =IF(D9="","",IF(AND(G90,G100),"",IF(G9=G10,G9,G 10)+IF(E10=0,0,E10))) I think your #VALUE error arose because you were sometimes trying to add "" plus a number. Hope this helps, Hutch "TammyS" wrote: I'm working on an IF formula: =IF(D9="","",IF(AND(G90,G100),"",IF(G9=G10,G9,G 10))+IF(E10=0,"",E10)) G9 and G10 can be positive or negative. If G9 or G10 is a negative number, I want the greater of the two to be used and then to be added to E10. But right now, Excel is saying -20 (which is G10) is greater than zero. I'm getting a VALUE error. Thanks. TammyS |
Negative numbers in an IF formula
When I used the the step by step function to figure out the error, it said
that G100 was TRUE even though G10 was -20. "SimonCC" wrote: First of all, the formula will result in an error whenever any of the IF condition evaluates to "", because addition (+) doesn't work with "" (empty string). So basically there are 4 possible final results from the formula: "" + "" = error number + "" = error "" + number = error number + number = number Do you actually just want a "" as a final result instead of a partal result? If so, try: =IF(D9="","",IF(E10=0,"",IF(AND(G90,G100),"",IF( G9=G10,G9+E10,G10+E10)))) Secondly, what tells you that Excel is saying G10 (-20) is greater than zero? Sorry I couldn't figure that part out. "TammyS" wrote: I'm working on an IF formula: =IF(D9="","",IF(AND(G90,G100),"",IF(G9=G10,G9,G 10))+IF(E10=0,"",E10)) G9 and G10 can be positive or negative. If G9 or G10 is a negative number, I want the greater of the two to be used and then to be added to E10. But right now, Excel is saying -20 (which is G10) is greater than zero. I'm getting a VALUE error. Thanks. TammyS |
Negative numbers in an IF formula
Hi,
I appreciate everyone's help with this. But it's still returning only positive numbers. G7 is 10 G8 is -20 E8 is 0 E7 (the result) is 10 (should be -20) G11 is blank G12 is -10 E12 is -50 E11 (the result) is blank (should be -60) G13 is -10 G14 is -10 E14 is -40 E13 (the result) is 0 (should be -50) "Bearacade" wrote: You are getting a #value because of the E10 comment. Change it to IF(E10=0,0,E10)) Another thing is you might want to use large instead of the the IF command, I think it's cleaner: Personally, you don't even need the whole E10 thing.. =IF(D9="","",IF(AND(G90,G100),"",LARGE(G9:G10,1) +E10)) It's cleaner this way.. you will STILL get a #Value if anything other than number is in E10.. You can protect against that with: =IF(D9="","",IF(AND(G90,G100),"",LARGE(G9:G10,1) +IF(ISNUMBER(E10),E10,0))) -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=563067 |
Negative numbers in an IF formula
You need to be more consisent with what you are telling us and your data.. nowhere in your original formula did it show any of the cell you are looking at. I am going to attempt to break down what you are saying.. I am going to have to assume that G9 is (A), G10 is (B) and E10 is (C) G7 (A) is 10 , G8 (B) is -20 E8 (C) is 0, E7 is 10, should be -20, Your orginal formula ask for the largest of the number which is 10 (10 -20), that's why your answer is 10 G11 (A) is blank, G12 (B) is -10, E12 (C) is -50, should be -60, and that is what I am showing G13 (A) is -10, G14 (B) is -10, E14 (C) is -40, should be -50 and that is what I am showing.. I suggest you recheck your formula when you drag and fill and make sure that you are referencing the right cells TammyS Wrote: Hi, I appreciate everyone's help with this. But it's still returning only positive numbers. G7 is 10 G8 is -20 E8 is 0 E7 (the result) is 10 (should be -20) G11 is blank G12 is -10 E12 is -50 E11 (the result) is blank (should be -60) G13 is -10 G14 is -10 E14 is -40 E13 (the result) is 0 (should be -50) [\QUOTE] -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=563067 |
Negative numbers in an IF formula
Not really sure what happened to cells in column D in your original formula.
Let me just attempt this based on the examples you gave: =MIN(G7:G8)+E8 And the formula will remain this simple until you can provide a different example which would make the formula wrong. -Simon "TammyS" wrote: Hi, I appreciate everyone's help with this. But it's still returning only positive numbers. G7 is 10 G8 is -20 E8 is 0 E7 (the result) is 10 (should be -20) G11 is blank G12 is -10 E12 is -50 E11 (the result) is blank (should be -60) G13 is -10 G14 is -10 E14 is -40 E13 (the result) is 0 (should be -50) "Bearacade" wrote: You are getting a #value because of the E10 comment. Change it to IF(E10=0,0,E10)) Another thing is you might want to use large instead of the the IF command, I think it's cleaner: Personally, you don't even need the whole E10 thing.. =IF(D9="","",IF(AND(G90,G100),"",LARGE(G9:G10,1) +E10)) It's cleaner this way.. you will STILL get a #Value if anything other than number is in E10.. You can protect against that with: =IF(D9="","",IF(AND(G90,G100),"",LARGE(G9:G10,1) +IF(ISNUMBER(E10),E10,0))) -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=563067 |
Negative numbers in an IF formula
"TammyS" wrote in message
... "SimonCC" wrote: ... Secondly, what tells you that Excel is saying G10 (-20) is greater than zero? Sorry I couldn't figure that part out. When I used the the step by step function to figure out the error, it said that G100 was TRUE even though G10 was -20. Are you sure that G10 is a number, not text? -- David Biddulph |
Negative numbers in an IF formula
Sorry but it didn't seem the problem was with col. D. Substitute 9 and 10
for the numbers in the example I gave (it's the same formula down the page just using different scenarios to make sure the formula is correct). =IF(D9="","",IF(AND(G90,G100),"",LARGE(G9:G10,1) +E10)) G9 is -10 G10 is 10 E10 is 0 E9 (the result) is 0 (should be -10) "SimonCC" wrote: Not really sure what happened to cells in column D in your original formula. Let me just attempt this based on the examples you gave: =MIN(G7:G8)+E8 And the formula will remain this simple until you can provide a different example which would make the formula wrong. -Simon "TammyS" wrote: Hi, I appreciate everyone's help with this. But it's still returning only positive numbers. G7 is 10 G8 is -20 E8 is 0 E7 (the result) is 10 (should be -20) G11 is blank G12 is -10 E12 is -50 E11 (the result) is blank (should be -60) G13 is -10 G14 is -10 E14 is -40 E13 (the result) is 0 (should be -50) "Bearacade" wrote: You are getting a #value because of the E10 comment. Change it to IF(E10=0,0,E10)) Another thing is you might want to use large instead of the the IF command, I think it's cleaner: Personally, you don't even need the whole E10 thing.. =IF(D9="","",IF(AND(G90,G100),"",LARGE(G9:G10,1) +E10)) It's cleaner this way.. you will STILL get a #Value if anything other than number is in E10.. You can protect against that with: =IF(D9="","",IF(AND(G90,G100),"",LARGE(G9:G10,1) +IF(ISNUMBER(E10),E10,0))) -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=563067 |
Negative numbers in an IF formula
Let me start over again by including some cells that relate to the original
formula but aren't in the original formula (maybe the problem is in one of the other formulas): The formula in E9 is =IF(D9="","",IF(AND(G90,G100),"",LARGE(G9:G10,1) +E10)) There is no formula in D9 but the number is 940 The formula in G9 is =IF(D9="","",D10-D9) There is no formula in D10 but the number is 950 The formula in G10 is =D10-F10 There is no formula in F10 but the number is 960 The formula in E10 is =Q10-D10 The formula in Q10 is =IF(A10<C10,A10,D10) There is no formula in A10 but the number is 1000 The formula in C10 is =D10 Thanks "Bearacade" wrote: You need to be more consisent with what you are telling us and your data.. nowhere in your original formula did it show any of the cell you are looking at. I am going to attempt to break down what you are saying.. I am going to have to assume that G9 is (A), G10 is (B) and E10 is (C) G7 (A) is 10 , G8 (B) is -20 E8 (C) is 0, E7 is 10, should be -20, Your orginal formula ask for the largest of the number which is 10 (10 -20), that's why your answer is 10 G11 (A) is blank, G12 (B) is -10, E12 (C) is -50, should be -60, and that is what I am showing G13 (A) is -10, G14 (B) is -10, E14 (C) is -40, should be -50 and that is what I am showing.. I suggest you recheck your formula when you drag and fill and make sure that you are referencing the right cells TammyS Wrote: Hi, I appreciate everyone's help with this. But it's still returning only positive numbers. G7 is 10 G8 is -20 E8 is 0 E7 (the result) is 10 (should be -20) G11 is blank G12 is -10 E12 is -50 E11 (the result) is blank (should be -60) G13 is -10 G14 is -10 E14 is -40 E13 (the result) is 0 (should be -50) [\QUOTE] -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=563067 |
Negative numbers in an IF formula
Don't know how that formula returns 0. With those values, that formula is
essentially G10+E10 = 10 + 0 = 10 -Simon "TammyS" wrote: Sorry but it didn't seem the problem was with col. D. Substitute 9 and 10 for the numbers in the example I gave (it's the same formula down the page just using different scenarios to make sure the formula is correct). =IF(D9="","",IF(AND(G90,G100),"",LARGE(G9:G10,1) +E10)) G9 is -10 G10 is 10 E10 is 0 E9 (the result) is 0 (should be -10) "SimonCC" wrote: Not really sure what happened to cells in column D in your original formula. Let me just attempt this based on the examples you gave: =MIN(G7:G8)+E8 And the formula will remain this simple until you can provide a different example which would make the formula wrong. -Simon "TammyS" wrote: Hi, I appreciate everyone's help with this. But it's still returning only positive numbers. G7 is 10 G8 is -20 E8 is 0 E7 (the result) is 10 (should be -20) G11 is blank G12 is -10 E12 is -50 E11 (the result) is blank (should be -60) G13 is -10 G14 is -10 E14 is -40 E13 (the result) is 0 (should be -50) "Bearacade" wrote: You are getting a #value because of the E10 comment. Change it to IF(E10=0,0,E10)) Another thing is you might want to use large instead of the the IF command, I think it's cleaner: Personally, you don't even need the whole E10 thing.. =IF(D9="","",IF(AND(G90,G100),"",LARGE(G9:G10,1) +E10)) It's cleaner this way.. you will STILL get a #Value if anything other than number is in E10.. You can protect against that with: =IF(D9="","",IF(AND(G90,G100),"",LARGE(G9:G10,1) +IF(ISNUMBER(E10),E10,0))) -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=563067 |
Negative numbers in an IF formula
You really need to check your formulas... You are getting a 0 because that is how your formula is calling it. Your orginal formula is IF(G9=G10,G9,G10), which basically look for the larger of two number in G9 and G10. That's why I replaced it with Large(G9:G10,1) G9 is -10 and G10 is 10, so the larger number is 10 If you use your original formula, IF(-10=10 (which is false), -10, 10) So you would still get 10 as your answer... TammyS Wrote: Sorry but it didn't seem the problem was with col. D. Substitute 9 and 10 for the numbers in the example I gave (it's the same formula down the page just using different scenarios to make sure the formula is correct). =IF(D9="","",IF(AND(G90,G100),"",LARGE(G9:G10,1) +E10)) G9 is -10 G10 is 10 E10 is 0 E9 (the result) is 0 (should be -10) -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=563067 |
Negative numbers in an IF formula
You really need to check your formulas... You are getting a 0 because that is how your formula is calling it. Your orginal formula is IF(G9=G10,G9,G10), which basically look for the larger of two number in G9 and G10. That's why I replaced it with Large(G9:G10,1) G9 is -10 and G10 is 10, so the larger number is 10 If you use your original formula, IF(-10=10 (which is false), -10, 10) So you would still get 10 as your answer... TammyS Wrote: Sorry but it didn't seem the problem was with col. D. Substitute 9 and 10 for the numbers in the example I gave (it's the same formula down the page just using different scenarios to make sure the formula is correct). =IF(D9="","",IF(AND(G90,G100),"",LARGE(G9:G10,1) +E10)) G9 is -10 G10 is 10 E10 is 0 E9 (the result) is 0 (should be -10) -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=563067 |
Negative numbers in an IF formula
IF(AND(G90,G100),"",LARGE(G9:G10,1)
The first part of the formula was supposed to remove any numbers greater than zero but now I see that formula is wrong: IF(AND(G90,G100),"" My intention was to get rid of anything greater than zero in the first part of the formula, then figure out which of the negative numbers (if any) was the greater of the two in the second part of the formula and return that number. The current formula will only return a blank if both numbers are greater than zero. "Bearacade" wrote: You really need to check your formulas... You are getting a 0 because that is how your formula is calling it. Your orginal formula is IF(G9=G10,G9,G10), which basically look for the larger of two number in G9 and G10. That's why I replaced it with Large(G9:G10,1) G9 is -10 and G10 is 10, so the larger number is 10 If you use your original formula, IF(-10=10 (which is false), -10, 10) So you would still get 10 as your answer... TammyS Wrote: Sorry but it didn't seem the problem was with col. D. Substitute 9 and 10 for the numbers in the example I gave (it's the same formula down the page just using different scenarios to make sure the formula is correct). =IF(D9="","",IF(AND(G90,G100),"",LARGE(G9:G10,1) +E10)) G9 is -10 G10 is 10 E10 is 0 E9 (the result) is 0 (should be -10) -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=563067 |
Negative numbers in an IF formula
Ok.. let's redo the formula then, I don't know of a function that looks for the largest value that meets a certain criteria. So unless someone enlighten me, I will have to nest a few Ifs together: =IF(D9="","",IF(AND(G90,G100),"",IF(AND(G9<0, G100), G9+E10, IF(AND(G10<0, G90), G10+E10, LARGE(G9:G10,1)+E10)))) -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=563067 |
Negative numbers in an IF formula
Part of the problem was that in an earlier attempt to fix it, I changed the
format to a customized one and I forgot to change it back. It's a number format now. Currently, I have =IF(D9="","",IF(AND(G90,G100),"",MIN(G9:G10))+E1 0) This returns the -10 but only if there is a number in D9. The purpose of D9 is if the number in D10 is changed - I wanted D9 to reflect the original number. Now it looks like I have to rephrase that part of the formula. "SimonCC" wrote: Don't know how that formula returns 0. With those values, that formula is essentially G10+E10 = 10 + 0 = 10 -Simon "TammyS" wrote: Sorry but it didn't seem the problem was with col. D. Substitute 9 and 10 for the numbers in the example I gave (it's the same formula down the page just using different scenarios to make sure the formula is correct). =IF(D9="","",IF(AND(G90,G100),"",LARGE(G9:G10,1) +E10)) G9 is -10 G10 is 10 E10 is 0 E9 (the result) is 0 (should be -10) "SimonCC" wrote: Not really sure what happened to cells in column D in your original formula. Let me just attempt this based on the examples you gave: =MIN(G7:G8)+E8 And the formula will remain this simple until you can provide a different example which would make the formula wrong. -Simon "TammyS" wrote: Hi, I appreciate everyone's help with this. But it's still returning only positive numbers. G7 is 10 G8 is -20 E8 is 0 E7 (the result) is 10 (should be -20) G11 is blank G12 is -10 E12 is -50 E11 (the result) is blank (should be -60) G13 is -10 G14 is -10 E14 is -40 E13 (the result) is 0 (should be -50) "Bearacade" wrote: You are getting a #value because of the E10 comment. Change it to IF(E10=0,0,E10)) Another thing is you might want to use large instead of the the IF command, I think it's cleaner: Personally, you don't even need the whole E10 thing.. =IF(D9="","",IF(AND(G90,G100),"",LARGE(G9:G10,1) +E10)) It's cleaner this way.. you will STILL get a #Value if anything other than number is in E10.. You can protect against that with: =IF(D9="","",IF(AND(G90,G100),"",LARGE(G9:G10,1) +IF(ISNUMBER(E10),E10,0))) -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=563067 |
Negative numbers in an IF formula
I think I figured it out:
E9 is =IF(AND(G9=0,G10=0),"",MIN(G9:G10)+E10) and G9 is =IF(D9="","",D10-D9) Thanks for everyone's input. "Bearacade" wrote: You really need to check your formulas... You are getting a 0 because that is how your formula is calling it. Your orginal formula is IF(G9=G10,G9,G10), which basically look for the larger of two number in G9 and G10. That's why I replaced it with Large(G9:G10,1) G9 is -10 and G10 is 10, so the larger number is 10 If you use your original formula, IF(-10=10 (which is false), -10, 10) So you would still get 10 as your answer... TammyS Wrote: Sorry but it didn't seem the problem was with col. D. Substitute 9 and 10 for the numbers in the example I gave (it's the same formula down the page just using different scenarios to make sure the formula is correct). =IF(D9="","",IF(AND(G90,G100),"",LARGE(G9:G10,1) +E10)) G9 is -10 G10 is 10 E10 is 0 E9 (the result) is 0 (should be -10) -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=563067 |
| All times are GMT +1. The time now is 05:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com