Formula

[BSantos]

=IF(AND(G6=0,O6=0),P6,IF(G6=0,O6,G6-N6))

The above formula is in cell Q6 the results is a negative number. example
-2. I want to add to this formula above that if the results is less than Zero
than give me Zero.

Any help!

B.

[soxcpa@gmail.com]

You can use the MAX function make it only give you 0 and higher as
follows:

=MAX ( ##, 0)

thus:
=MAX(IF(AND(G6=0,O6=0),P6,IF(G6=0,O6,G6-N6)),0)



BSantos wrote:
> =IF(AND(G6=0,O6=0),P6,IF(G6=0,O6,G6-N6))
>
> The above formula is in cell Q6 the results is a negative number. example
> -2. I want to add to this formula above that if the results is less than Zero
> than give me Zero.
>
> Any help!
>
> B.

[Sandy Mann]

Assuming that you mean G6-N6 is negative try:

=IF(AND(G6=0,O6=0),P6,IF(G6=0,O6,MAX(0,G6-N6)))


--
HTH

Sandy
sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk


"BSantos" <BSantos@discussions.microsoft.com> wrote in message
news:BAC67386-D6A0-479C-9F98-33AE85813782@microsoft.com...
> =IF(AND(G6=0,O6=0),P6,IF(G6=0,O6,G6-N6))
>
> The above formula is in cell Q6 the results is a negative number. example
> -2. I want to add to this formula above that if the results is less than
> Zero
> than give me Zero.
>
> Any help!
>
> B.

[daddylonglegs]

try this formula

=MAX(0,IF(G6,G6-N6,IF(O6,O6,P6))

[BSantos]

Sandy, could you help me with this one:

=IF($I$2="Active",VLOOKUP($E6,Data!$G$4:$AT$1587,28,0),"0")

I have this formula above in a cell. I want to add that if the vlookup
doesn't find anything and returns a #N/A than give me ZERO.

I'm doing this so I don't get #n/a in the column and my totals will add up
without giving me #n/a.

"Sandy Mann" wrote:

> Assuming that you mean G6-N6 is negative try:
>
> =IF(AND(G6=0,O6=0),P6,IF(G6=0,O6,MAX(0,G6-N6)))
>
>
> --
> HTH
>
> Sandy
> sandymann2@mailinator.com
> Replace@mailinator.com with @tiscali.co.uk
>
>
> "BSantos" <BSantos@discussions.microsoft.com> wrote in message
> news:BAC67386-D6A0-479C-9F98-33AE85813782@microsoft.com...
> > =IF(AND(G6=0,O6=0),P6,IF(G6=0,O6,G6-N6))
> >
> > The above formula is in cell Q6 the results is a negative number. example
> > -2. I want to add to this formula above that if the results is less than
> > Zero
> > than give me Zero.
> >
> > Any help!
> >
> > B.
>
>
>

[daddylonglegs]

You can do it this way

=IF(OR($I$2="Active",ISNA(VLOOKUP($E6,Data!$G$4:$AT$1587,28,0))),0,VLOOKUP($E6,Data!$G$4:$AT$1587,28,0))