ROUND CENTS TO EITHER 49 OR 99 AMOUNTS

We manage prices of thousands of items that are especially volatile
currently. We want to round all cents <.49 as .49 and >.50 as .99. As the
cell contents are already a formula result, I'd like to "tack on" something
else, if possible, rather than adding another column. I've reviewed the
current "rounding" topics and can't quite find the working of conditionals..
Thanks All from a new participant!
--
TENNISPLAYER

Hi!

What if a price is $15.00, do you want that rounded to $15.49?

Try this:

=IF(MOD(your_formula,1)<0.5,INT(your_formula)+0.49,INT(your_formula)+0.99)

Biff

"PRICEMGR" <[email protected]> wrote in message
news:[email protected]...
> We manage prices of thousands of items that are especially volatile
> currently. We want to round all cents <.49 as .49 and >.50 as .99. As the
> cell contents are already a formula result, I'd like to "tack on"
> something
> else, if possible, rather than adding another column. I've reviewed the
> current "rounding" topics and can't quite find the working of
> conditionals..
> Thanks All from a new participant!
> --
> TENNISPLAYER

Hi

Maybe
=ROUND(A1+0.01;1)-0.01


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"PRICEMGR" <[email protected]> wrote in message
news:[email protected]...
> We manage prices of thousands of items that are especially volatile
> currently. We want to round all cents <.49 as .49 and >.50 as .99. As the
> cell contents are already a formula result, I'd like to "tack on"
> something
> else, if possible, rather than adding another column. I've reviewed the
> current "rounding" topics and can't quite find the working of
> conditionals..
> Thanks All from a new participant!
> --
> TENNISPLAYER

THIS IS A GREAT SOLUTION! For both signage and to denote for sales staff the
current years inventory, we use pricing levels that rotate common endings.
Next year maybe ..98 or .97, etc. This solution automates what used to be
ALOT of hand tuning of our excel based price tables!
Many Thanks!
--
TENNISPLAYER


"Biff" wrote:

> Hi!
>
> What if a price is $15.00, do you want that rounded to $15.49?
>
> Try this:
>
> =IF(MOD(your_formula,1)<0.5,INT(your_formula)+0.49,INT(your_formula)+0.99)
>
> Biff
>
> "PRICEMGR" <[email protected]> wrote in message
> news:[email protected]...
> > We manage prices of thousands of items that are especially volatile
> > currently. We want to round all cents <.49 as .49 and >.50 as .99. As the
> > cell contents are already a formula result, I'd like to "tack on"
> > something
> > else, if possible, rather than adding another column. I've reviewed the
> > current "rounding" topics and can't quite find the working of
> > conditionals..
> > Thanks All from a new participant!
> > --
> > TENNISPLAYER
>
>
>

Thanks for responding! I entered the formula result of my pricing routine as
"A1" in your function and received an error. I'm trying to puzzle out how to
fix the problem. I have received one solution that works, but I know there is
often many alternates to a problem and I want to learn from them all...
The error highlights "0.01" in the middle of the function statement...
--
TENNISPLAYER


"Arvi Laanemets" wrote:

> Hi
>
> Maybe
> =ROUND(A1+0.01;1)-0.01
>
>
> --
> Arvi Laanemets
> ( My real mail address: arvil<at>tarkon.ee )
>
>
> "PRICEMGR" <[email protected]> wrote in message
> news:[email protected]...
> > We manage prices of thousands of items that are especially volatile
> > currently. We want to round all cents <.49 as .49 and >.50 as .99. As the
> > cell contents are already a formula result, I'd like to "tack on"
> > something
> > else, if possible, rather than adding another column. I've reviewed the
> > current "rounding" topics and can't quite find the working of
> > conditionals..
> > Thanks All from a new participant!
> > --
> > TENNISPLAYER
>
>
>

TENNISPLAYER,

The error was the use of ; rather than , (which is the separation character used by the author), but
that formula wouldn't have worked for you anyway. A similar formula that would work is

=ROUNDUP(2*(A1+0.01),0)/2-0.01

HTH,
Bernie
MS Excel MVP


"PRICEMGR" <[email protected]> wrote in message
news:[email protected]...
> Thanks for responding! I entered the formula result of my pricing routine as
> "A1" in your function and received an error. I'm trying to puzzle out how to
> fix the problem. I have received one solution that works, but I know there is
> often many alternates to a problem and I want to learn from them all...
> The error highlights "0.01" in the middle of the function statement...
> --
> TENNISPLAYER
>
>
> "Arvi Laanemets" wrote:
>
>> Hi
>>
>> Maybe
>> =ROUND(A1+0.01;1)-0.01
>>
>>
>> --
>> Arvi Laanemets
>> ( My real mail address: arvil<at>tarkon.ee )
>>
>>
>> "PRICEMGR" <[email protected]> wrote in message
>> news:[email protected]...
>> > We manage prices of thousands of items that are especially volatile
>> > currently. We want to round all cents <.49 as .49 and >.50 as .99. As the
>> > cell contents are already a formula result, I'd like to "tack on"
>> > something
>> > else, if possible, rather than adding another column. I've reviewed the
>> > current "rounding" topics and can't quite find the working of
>> > conditionals..
>> > Thanks All from a new participant!
>> > --
>> > TENNISPLAYER
>>
>>
>>

Suggest a formula for the particular output.


A value between, their output should be

0 - 50 : 49
51-100 : 99
101-150 :149
151-200 : 199
201-250 : 249
251-300 : 299

and so on