Make a negative response to if question equal zero

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
Click to expand...

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
 
Thanks. That did it!

Franz Verga said:
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
Click to expand...
 
Ced said:
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.
Click to expand...

=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
Click to expand...

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)
Click to expand...

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.
 
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
 
Back
Top