Negative numbers in an IF formula

[TammyS]

I'm working on an IF formula:

=IF(D9="","",IF(AND(G9>0,G10>0),"",IF(G9>=G10,G9,G10))+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

 

[Bearacade]

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(G9>0,G10>0),"",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(G9>0,G10>0),"",LARGE(G9:G10,1)+IF(ISNUMBER(E10),E10,0)))

 

[Tom Hutchins]

Try
=IF(D9="","",IF(AND(G9>0,G10>0),"",IF(G9>=G10,G9,G10)+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

 

[SimonCC]

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(G9>0,G10>0),"",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]

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.

 

[TammyS]

When I used the the step by step function to figure out the error, it said
that G10>0 was TRUE even though G10 was -20.

 

[TammyS]

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]

You need to be more consisent with what you are telling us and you
data.. nowhere in your original formula did it show any of the cell yo
are looking at.

I am going to attempt to break down what you are saying.. I am going t
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 (1

-20), that's why your answer is 10

G11 (A) is blank, G12 (B) is -10, E12 (C) is -50, should be -60, an
that is what I am showing

G13 (A) is -10, G14 (B) is -10, E14 (C) is -40, should be -50 and tha
is what I am showing..

I suggest you recheck your formula when you drag and fill and make sur
that you are referencing the right cells

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

 

[SimonCC]

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

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)

 

[David Biddulph]

When I used the the step by step function to figure out the error, it
said
that G10>0 was TRUE even though G10 was -20.

Are you sure that G10 is a number, not text?

 

[TammyS]

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(G9>0,G10>0),"",LARGE(G9:G10,1)+E10))

G9 is -10
G10 is 10
E10 is 0
E9 (the result) is 0 (should be -10)

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]

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(G9>0,G10>0),"",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

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

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]

 

[SimonCC]

Don't know how that formula returns 0. With those values, that formula is
essentially G10+E10 = 10 + 0 = 10

-Simon

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(G9>0,G10>0),"",LARGE(G9:G10,1)+E10))

G9 is -10
G10 is 10
E10 is 0
E9 (the result) is 0 (should be -10)

 

[Bearacade]

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 fo
the larger of two number in G9 and G10. That's why I replaced it wit
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...

 

[Bearacade]

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 fo
the larger of two number in G9 and G10. That's why I replaced it wit
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]

IF(AND(G9>0,G10>0),"",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(G9>0,G10>0),""
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]

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(G9>0,G10>0),"",IF(AND(G9<0, G10>0), G9+E10,
IF(AND(G10<0, G9>0), G10+E10, LARGE(G9:G10,1)+E10))))

 

[TammyS]

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(G9>0,G10>0),"",MIN(G9:G10))+E10)

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.

Don't know how that formula returns 0. With those values, that formula is
essentially G10+E10 = 10 + 0 = 10

-Simon

 

[TammyS]

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.