A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

ROUND CENTS TO EITHER 49 OR 99 AMOUNTS



 
 
Thread Tools Display Modes
  #1  
Old November 22nd 05, 07:15 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default 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
Ads
  #2  
Old November 22nd 05, 07:33 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default ROUND CENTS TO EITHER 49 OR 99 AMOUNTS

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" > wrote in message
...
> 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



  #3  
Old November 22nd 05, 07:53 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default ROUND CENTS TO EITHER 49 OR 99 AMOUNTS

Hi

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


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


"PRICEMGR" > wrote in message
...
> 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



  #4  
Old November 22nd 05, 03:49 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default ROUND CENTS TO EITHER 49 OR 99 AMOUNTS

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" > wrote in message
> ...
> > 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

>
>
>

  #5  
Old November 22nd 05, 04:10 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default ROUND CENTS TO EITHER 49 OR 99 AMOUNTS

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" > wrote in message
> ...
> > 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

>
>
>

  #6  
Old November 22nd 05, 04:32 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default ROUND CENTS TO EITHER 49 OR 99 AMOUNTS

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" > wrote in message
...
> 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" > wrote in message
>> ...
>> > 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

>>
>>
>>



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Round a number in nested function kim Excel Worksheet Functions 1 July 6th 05 11:45 AM
Round whole numbers up and down JoeBed Excel Discussion (Misc queries) 4 July 5th 05 05:21 PM
I am trying to round up to the nearest 25 cents sypher New Users to Excel 2 April 26th 05 01:14 AM
How do I ROUND() round off decimals of a column dataset? Højrup Excel Worksheet Functions 2 January 12th 05 11:50 AM
Amount or Numbers in Words ron New Users to Excel 6 December 24th 04 08:32 PM


All times are GMT +1. The time now is 08:58 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.