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)))

[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" 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(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)))
>
>
> --
> Bearacade
>
>
> ------------------------------------------------------------------------
> Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
> View this thread: http://www.excelforum.com/showthread...hreadid=563067
>
>

[Bearacade]

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. Try putting this formula in a blank sheet and just plug in the values, you will see that the results is what you are looking for.

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)

[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


"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
>
>

[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


"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
>
>

[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))))

[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

"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(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)))
> >
> >
> > --
> > Bearacade
> >
> >
> > ------------------------------------------------------------------------
> > Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
> > View this thread: http://www.excelforum.com/showthread...hreadid=563067
> >
> >

[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)


"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(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)))
> > >
> > >
> > > --
> > > Bearacade
> > >
> > >
> > > ------------------------------------------------------------------------
> > > Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
> > > View this thread: http://www.excelforum.com/showthread...hreadid=563067
> > >
> > >

[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 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...

I would also double check if the cell referencing is correct, cause you shouldn't be getting a 0, you should be getting 10

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)

[SimonCC]

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(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)
>
>
> "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(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)))
> > > >
> > > >
> > > > --
> > > > Bearacade
> > > >
> > > >
> > > > ------------------------------------------------------------------------
> > > > Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
> > > > View this thread: http://www.excelforum.com/showthread...hreadid=563067
> > > >
> > > >

[SimonCC]

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(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)
>
>
> "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(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)))
> > > >
> > > >
> > > > --
> > > > Bearacade
> > > >
> > > >
> > > > ------------------------------------------------------------------------
> > > > Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
> > > > View this thread: http://www.excelforum.com/showthread...hreadid=563067
> > > >
> > > >

[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

"TammyS" wrote:

> 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
>

[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.


"Tom Hutchins" wrote:

> 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
>
> "TammyS" wrote:
>
> > 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
> >

[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" wrote:

> 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
>

[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.

"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(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" wrote:
>
> > 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
> >

[David Biddulph]

"TammyS" <TammyS@discussions.microsoft.com> wrote in message
news:CD883A59-4AAE-4C68-8D36-85BB8492E339@microsoft.com...

> "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 G10>0 was TRUE even though G10 was -20.

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