Make a negative response to if question equal zero

[Ced]

I have a excel doc which takes
B9 ($12,000) represents value of product
B11 =sum(b9*.01) equals $1,200
B13 (125,000) represents mileage
B15 =IF(B13>0,1-(B13/100000),1)
B17 (25%) represents a modifier

B25 SUM(B11*B15*B17) represents final solution of -$75.00

My problem is this when B13 is over 100,000 the result in B15 is negative
which makes the final solution in B25 a negative number. If calculation in
B25 is negative I would like it to return a value of zero. Any help would be
appreciated.

[Franz Verga]

Nel post news:[email protected]
*Ced* ha scritto:

> I have a excel doc which takes
> B9 ($12,000) represents value of product
> B11 =sum(b9*.01) equals $1,200
> B13 (125,000) represents mileage
> B15 =IF(B13>0,1-(B13/100000),1)
> B17 (25%) represents a modifier
>
> B25 SUM(B11*B15*B17) represents final solution of -$75.00
>
> My problem is this when B13 is over 100,000 the result in B15 is
> negative which makes the final solution in B25 a negative number. If
> calculation in B25 is negative I would like it to return a value of
> zero. Any help would be appreciated.

In B25: =IF((B11*B15*B17)<0,0,(B11*B15*B17))


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

[Ced]

Thanks. That did it!

"Franz Verga" wrote:

> Nel post news:[email protected]
> *Ced* ha scritto:
>
> > I have a excel doc which takes
> > B9 ($12,000) represents value of product
> > B11 =sum(b9*.01) equals $1,200
> > B13 (125,000) represents mileage
> > B15 =IF(B13>0,1-(B13/100000),1)
> > B17 (25%) represents a modifier
> >
> > B25 SUM(B11*B15*B17) represents final solution of -$75.00
> >
> > My problem is this when B13 is over 100,000 the result in B15 is
> > negative which makes the final solution in B25 a negative number. If
> > calculation in B25 is negative I would like it to return a value of
> > zero. Any help would be appreciated.
>
> In B25: =IF((B11*B15*B17)<0,0,(B11*B15*B17))
>
>
> --
> Hope I helped you.
>
> Thanks in advance for your feedback.
>
> Ciao
>
> Franz Verga from Italy
>
>
>

[[email protected]]

Ced wrote:
> B25 SUM(B11*B15*B17) represents final solution of -$75.00
> [....]
> If calculation in B25 is negative I would like it to return a value
> of zero.

=max(0, b11*b15*b17)

> B13 (125,000) represents mileage
> B15 =IF(B13>0,1-(B13/100000),1)
> [....]
> My problem is this when B13 is over 100,000 the result in B15 is negative

So perhaps you should fix the problem at its source, namely one of the
following, whichever you prefer:

B15: =if(B13 > 0, max(0, 1 - B13/100000), 1)

B15: =max(0, min(1, 1 - B13/100000))

> B11 =sum(b9*.01)

Why are you using SUM(...) this way? That is a serious question.
Where did you apparently get the (incorrect) idea that formulas must be
a function?

They do not. B11 can simply be =B9*0.01, and B5 can simply be
=B11*B15*B17.

[Peo Sjoblom]

Or even

=MAX((B11*B15*B17),0)

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Franz Verga" <[email protected]> wrote in message
news:%23HI6pa%[email protected]...
> Nel post news:[email protected]
> *Ced* ha scritto:
>
>> I have a excel doc which takes
>> B9 ($12,000) represents value of product
>> B11 =sum(b9*.01) equals $1,200
>> B13 (125,000) represents mileage
>> B15 =IF(B13>0,1-(B13/100000),1)
>> B17 (25%) represents a modifier
>>
>> B25 SUM(B11*B15*B17) represents final solution of -$75.00
>>
>> My problem is this when B13 is over 100,000 the result in B15 is
>> negative which makes the final solution in B25 a negative number. If
>> calculation in B25 is negative I would like it to return a value of
>> zero. Any help would be appreciated.
>
> In B25: =IF((B11*B15*B17)<0,0,(B11*B15*B17))
>
>
> --
> Hope I helped you.
>
> Thanks in advance for your feedback.
>
> Ciao
>
> Franz Verga from Italy
>