Changing negative numbers to zero

Hi all,

Is there any way to automatically change any negative numbers in a
cell to zero? For a spend down spreadsheet that I am developing, any
negative numbers will be zero. I could not find a format or function
that would do it. I would like to avoid the =if(.......>0,......,0)
route because the formula in the cell already contains an if argument
and it is already too complicated. Would an array formula work? I
haven't worked with them too much. Any advice is appreciated.

TIA

Chet

Hi,

If those numbers (negative as well as positive) are the outcome of a
formula, you may modify the formula as,
=if(formula<0,0,formula)

Regards,
B. R. Ramachandran


"Chet T (Chet T)" wrote:

> Hi all,
>
> Is there any way to automatically change any negative numbers in a
> cell to zero? For a spend down spreadsheet that I am developing, any
> negative numbers will be zero. I could not find a format or function
> that would do it. I would like to avoid the =if(.......>0,......,0)
> route because the formula in the cell already contains an if argument
> and it is already too complicated. Would an array formula work? I
> haven't worked with them too much. Any advice is appreciated.
>
> TIA
>
> Chet
>

Hi,

I apologize for my earlier reply to your posting. I didn't notice that do
not want to use the IF function to change negative numbers to zero. Sorry.

Regards,
B. R. Ramachanrdan

"Chet T (Chet T)" wrote:

> Hi all,
>
> Is there any way to automatically change any negative numbers in a
> cell to zero? For a spend down spreadsheet that I am developing, any
> negative numbers will be zero. I could not find a format or function
> that would do it. I would like to avoid the =if(.......>0,......,0)
> route because the formula in the cell already contains an if argument
> and it is already too complicated. Would an array formula work? I
> haven't worked with them too much. Any advice is appreciated.
>
> TIA
>
> Chet
>

How about just:

=max(0,youroriginalformulahere)

If your formula returns -3, then 0 is larger and will be returned.

If your formula returns +33, then that's bigger than 0. So you'll see 33.

Chet T wrote:
>
> Hi all,
>
> Is there any way to automatically change any negative numbers in a
> cell to zero? For a spend down spreadsheet that I am developing, any
> negative numbers will be zero. I could not find a format or function
> that would do it. I would like to avoid the =if(.......>0,......,0)
> route because the formula in the cell already contains an if argument
> and it is already too complicated. Would an array formula work? I
> haven't worked with them too much. Any advice is appreciated.
>
> TIA
>
> Chet

--

Dave Peterson

Thanks Dave!

That did it.

Chet

On Thu, 06 Oct 2005 19:01:54 -0500, Dave Peterson
<[email protected]> wrote:

>How about just:
>
>=max(0,youroriginalformulahere)
>
>If your formula returns -3, then 0 is larger and will be returned.
>
>If your formula returns +33, then that's bigger than 0. So you'll see 33.
>
>Chet T wrote:
>>
>> Hi all,
>>
>> Is there any way to automatically change any negative numbers in a
>> cell to zero? For a spend down spreadsheet that I am developing, any
>> negative numbers will be zero. I could not find a format or function
>> that would do it. I would like to avoid the =if(.......>0,......,0)
>> route because the formula in the cell already contains an if argument
>> and it is already too complicated. Would an array formula work? I
>> haven't worked with them too much. Any advice is appreciated.
>>
>> TIA
>>
>> Chet
>
>--
>
>Dave Peterson

Thanks for the response B.R.

The formula already hase one if statement in it and I'm not sure that
I am good enough at excel yet to try to deal with two if statements.

Dave's =MAX(0,formula) response did the trick.

Thanks again.

Chet

On Thu, 6 Oct 2005 15:25:02 -0700, "B. R.Ramachandran"
<[email protected]> wrote:

>Hi,
>
>I apologize for my earlier reply to your posting. I didn't notice that do
>not want to use the IF function to change negative numbers to zero. Sorry.
>
>Regards,
>B. R. Ramachanrdan
>
>"Chet T (Chet T)" wrote:
>
>> Hi all,
>>
>> Is there any way to automatically change any negative numbers in a
>> cell to zero? For a spend down spreadsheet that I am developing, any
>> negative numbers will be zero. I could not find a format or function
>> that would do it. I would like to avoid the =if(.......>0,......,0)
>> route because the formula in the cell already contains an if argument
>> and it is already too complicated. Would an array formula work? I
>> haven't worked with them too much. Any advice is appreciated.
>>
>> TIA
>>
>> Chet
>>