cell formulae giving negative result.

[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

[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

"BrianC500" <[email protected]> wrote in message
news:[email protected]...
> 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

[Toppers]

what do you want the result to be if C2 < 10?

"BrianC500" wrote:

> 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

[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

"BrianC500" wrote:

> 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

[BrianC500]

Hi Bill,

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

Regards

Brian

"Bill Ridgeway" wrote:

> 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
>
> "BrianC500" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[BrianC500]

Hi,

I would like the result to be zero.

Thanks

Brian

"Toppers" wrote:

> what do you want the result to be if C2 < 10?
>
> "BrianC500" wrote:
>
> > 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

[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

"BrianC500" <[email protected]> wrote in message
news:[email protected]...
> Hi Bill,
>
> Thanks for the reply. I would like the fomula to return a figure of zero.
>
> Regards
>
> Brian
>
> "Bill Ridgeway" wrote:
>
>> 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
>>
>> "BrianC500" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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

[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

"Bill Ridgeway" wrote:

> 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
>
> "BrianC500" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Bill,
> >
> > Thanks for the reply. I would like the fomula to return a figure of zero.
> >
> > Regards
> >
> > Brian
> >
> > "Bill Ridgeway" wrote:
> >
> >> 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
> >>
> >> "BrianC500" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
>
>
>