FOrmula to work out pricing rounding up and down

[Carl]

I have a formula in cell that reads "=IF(C7>0,"FOC",-C7*1.175)" it
turns a negative into a Positive and a Positive FIgure into answer of
"FOC" Sometimes it will display a result of £2 or £5.50 or whatever
the figure when the original answer is a negative.

With me so far!!

The result which is displayed as a number i need it to round up t the
nearest 9.99.

For example

if c7 is £4.00 display answer of 9.99
if c7 is £11.00 display answer of 19.99
if c7 is £28.43 display answer of 29.99

So no matter what the answer is it rounds up to the 9.99 answer
automatically.

Please Help

 

[Sandy Mann]

Carl,

Try:

=IF(C7>=0,"FOC",CEILING(-C7*1.175,10)-0.01)

This will return "FOC" for zero or an empty cell. If you want a zero
returned in those circumstances then use:

=IF(C7>0,"FOC",MAX(0,CEILING(-C7*1.175,10)-0.01))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


I have a formula in cell that reads "=IF(C7>0,"FOC",-C7*1.175)" it
turns a negative into a Positive and a Positive FIgure into answer of
"FOC" Sometimes it will display a result of £2 or £5.50 or whatever
the figure when the original answer is a negative.

With me so far!!

The result which is displayed as a number i need it to round up t the
nearest 9.99.

For example

if c7 is £4.00 display answer of 9.99
if c7 is £11.00 display answer of 19.99
if c7 is £28.43 display answer of 29.99

So no matter what the answer is it rounds up to the 9.99 answer
automatically.

Please Help

 

[Carl]

Sandy:-

Many Thanks its taking me days to work on that. Thanks for your help

Carl

 

[Sandy Mann]

You're welcome - thanks for the feedback.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


Sandy:-

Many Thanks its taking me days to work on that. Thanks for your help

Carl