cell formulae giving negative result.

B

BrianC500

Hi,

I'm using the calculation IF(C2>=49.99,39.99*0.06,IF(C2<49.99(C2-10)*0.06))

How can I stop achieving a negative result when I put a figure of less than
10 in C2?

Any help very much appreciated

Brian
 
B

Bill Ridgeway

Firstly there is an error in the formula -
IF(C2>=49.99,39.99*0.06,IF(C2<49.99(C2-10)*0.06))
IF(C2>=49.99,39.99*0.06,IF(C2<49.99,(C2-10)*0.06))

Now, to answer the question. You can't stop the formula returning a
negative number because it is a mathematical function. You can, however,
change what it does when it gets a negative number. What do you want to
happen when the formula returns a negative number?

Regards.

Bill Ridgeway
Computer Solutions
 
G

Greg Wilson

The second IF function (nested) isn't necessary since if C2 failed to meet
the first criterion where C2>=49.99 then it is mandated to be less than
49.99. Also, since 39.99*0.06 is a hard number, the second of the two formula
options below lists this number (2.399) instead:

=If(C2>=49.99, 39.99*0.06, Max(C2-10, 0)*0.06)
=IF(C2>=49.99,2.399,MAX(C2-10,0)*0.06)

Regards,
Greg
 
B

BrianC500

Hi Bill,

Thanks for the reply. I would like the fomula to return a figure of zero.

Regards

Brian
 
B

Bill Ridgeway

I think the formula you want is -
=IF(C2>=49.99,39.99*0.06,IF((C2-10)*0.06<0,0,C2-10)*0.06)

Regards.

Bill Ridgeway
Computer Solutions
 
B

BrianC500

Hi Bill,

Thank you very much for all your help. That calculation was perfect. I can
press on with my spreadsheet now.

Regards

Brian
 
Top