Make a negative response to if question equal zero

[Guest]

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

 

[Guest]

Thanks. That did it!

Nel post *Ced* ha scritto:


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

 

[joeu2004]

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