rounding #9

is there a way to have a column round automatically instead of having to
round onto another column?  I don't want to add another column to just
be able to round.


-- 
denhar
------------------------------------------------------------------------
denhar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24089
View this thread: http://www.excelforum.com/showthread.php?threadid=378356

0
6/12/2005 2:50:19 AM
excel 39879 articles. 2 followers. Follow

6 Replies
762 Views

Similar Articles

[PageSpeed] 26

If the column contains formulas, the formulas can incorporate the Round()
function within themselves.

For example, if the formula is:

=A1*B1

it could be revised to:

=ROUND(A1*B1,2)

Is this what you're looking for?
-- 
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"denhar" <denhar.1qhtma_1118545501.0257@excelforum-nospam.com> wrote in
message news:denhar.1qhtma_1118545501.0257@excelforum-nospam.com...
>
> is there a way to have a column round automatically instead of having to
> round onto another column?  I don't want to add another column to just
> be able to round.
>
>
> -- 
> denhar
> ------------------------------------------------------------------------
> denhar's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=24089
> View this thread: http://www.excelforum.com/showthread.php?threadid=378356
>

0
ragdyer1 (4060)
6/12/2005 3:50:00 AM
What formulas are you using to perform the rounding?

Just a thought ... Are you rounding ... OR ... formatting?
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
news:uCuURHwbFHA.2124@TK2MSFTNGP14.phx.gbl...
> If the column contains formulas, the formulas can incorporate the Round()
> function within themselves.
>
> For example, if the formula is:
>
> =A1*B1
>
> it could be revised to:
>
> =ROUND(A1*B1,2)
>
> Is this what you're looking for?
> -- 
> HTH,
>
> RD
>
> --------------------------------------------------------------------------
-
> Please keep all correspondence within the NewsGroup, so all may benefit !
> --------------------------------------------------------------------------
-
> "denhar" <denhar.1qhtma_1118545501.0257@excelforum-nospam.com> wrote in
> message news:denhar.1qhtma_1118545501.0257@excelforum-nospam.com...
> >
> > is there a way to have a column round automatically instead of having to
> > round onto another column?  I don't want to add another column to just
> > be able to round.
> >
> >
> > -- 
> > denhar
> > ------------------------------------------------------------------------
> > denhar's Profile:
> http://www.excelforum.com/member.php?action=getinfo&userid=24089
> > View this thread:
http://www.excelforum.com/showthread.php?threadid=378356
> >
>

0
ragdyer1 (4060)
6/12/2005 3:56:44 AM
Darn it - Wrong Thread !!!
"Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
news:ewPNCLwbFHA.724@TK2MSFTNGP12.phx.gbl...
> What formulas are you using to perform the rounding?
>
> Just a thought ... Are you rounding ... OR ... formatting?
> -- 
> Regards,
>
> RD
>
> --------------------------------------------------------------------------
-
> Please keep all correspondence within the NewsGroup, so all may benefit !
> --------------------------------------------------------------------------
-
> "Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
> news:uCuURHwbFHA.2124@TK2MSFTNGP14.phx.gbl...
> > If the column contains formulas, the formulas can incorporate the
Round()
> > function within themselves.
> >
> > For example, if the formula is:
> >
> > =A1*B1
> >
> > it could be revised to:
> >
> > =ROUND(A1*B1,2)
> >
> > Is this what you're looking for?
> > -- 
> > HTH,
> >
> > RD
> >
>
> --------------------------------------------------------------------------
> -
> > Please keep all correspondence within the NewsGroup, so all may benefit
!
>
> --------------------------------------------------------------------------
> -
> > "denhar" <denhar.1qhtma_1118545501.0257@excelforum-nospam.com> wrote in
> > message news:denhar.1qhtma_1118545501.0257@excelforum-nospam.com...
> > >
> > > is there a way to have a column round automatically instead of having
to
> > > round onto another column?  I don't want to add another column to just
> > > be able to round.
> > >
> > >
> > > -- 
> > > denhar
> >
> ------------------------------------------------------------------------
> > > denhar's Profile:
> > http://www.excelforum.com/member.php?action=getinfo&userid=24089
> > > View this thread:
> http://www.excelforum.com/showthread.php?threadid=378356
> > >
> >
>

0
ragdyer1 (4060)
6/12/2005 4:00:53 AM
as dumb as this sounds, sometimes I really would like to be able to put
in $1.49 and it automatically change to $1 or $1.51 to $2.  I have a
long list of numbers to enter from a bunch of sources so having to stop
and decipher takes too long.  If I tell the column to not use decimal
places which accomplishes the same thing only it doesn't give me the
..00 that I need at the end.


-- 
denhar
------------------------------------------------------------------------
denhar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24089
View this thread: http://www.excelforum.com/showthread.php?threadid=378356

0
6/12/2005 12:28:58 PM
If I understand you correctly, here are some thoughts . . .

First, use a custom format on your input cells:

Format>Cells>Number
Category: Custom
Type: 0".00"

That will  make the numbers look like they are rounded to the nearest
digit, but will still display 2 decimal places.

However...If you the sum of those cells to behave as if all of the
values ARE rounded, you'll need a different kind of SUM function.
(Since they will contain the actual inputs (1.51 will look like 2.00,
but will still be 1.51)

Try this for your totals (assuming inputs are in A1:A10):
=SUMPRODUCT(ROUND(A1:A10,0))

That function sums the rounded numbers.

Does that help?

Ron


-- 
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21419
View this thread: http://www.excelforum.com/showthread.php?threadid=378356

0
6/12/2005 1:24:34 PM
I will try that - thanks.


-- 
denhar
------------------------------------------------------------------------
denhar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24089
View this thread: http://www.excelforum.com/showthread.php?threadid=378356

0
6/12/2005 1:27:55 PM
Reply:

Similar Artilces:

rounding #9
is there a way to have a column round automatically instead of having to round onto another column? I don't want to add another column to just be able to round. -- denhar ------------------------------------------------------------------------ denhar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24089 View this thread: http://www.excelforum.com/showthread.php?threadid=378356 If the column contains formulas, the formulas can incorporate the Round() function within themselves. For example, if the formula is: =A1*B1 it could be revised to: =ROUND(A1*...

round() ?
Hello How can i round float to 2 digits after '.' ? Thanx Quick and Dirty: Multiply by 100, add 0.5 (to round up correctly). convert to long, then back to float, then divide by 100. This is limited to values that will fit within a long, and is not efficient. It is reliable. also: float val; val = val - fmod(val, .01); Or, the real thing: char *_fcvt( double value, int count, int *dec, int *sign ); Which is a char string, which you can then feed to atof() and get your rounded value back. Alas, beware errors in precision limits! Balboos user@domain.invalid wrote: >...

Rounding question
I'm using Excel 2007 but 'save as' 97-2003 as colleagues have different versions. All number formats are set to 2 decimal places. I'm finding that percentage calculations are rounding up to 2 decimal places but when the result in currency is subtracted from another figure the sum is rounded down. This gives results such as 6 - 3 = 4. I'm sure there must be a simple answer but I can't find it. Hoping someone here knows, as checking all simple calcuations is getting ridiculous ! Carrie "Carrie" <pantscarolyn.smith2@ntlworld.com> w...

Problem with rounding currency values
Hi, I am using the Sales Global Procedure SOP_Calculate_Trade_Discount_Split. This procedure returns the line trade discount. The line trade discount returned is rounded off. eg: if value is 6.76 then it returns 7.00. Is there some place where i can specify to turn off the rounding off? The output type is Currency. Any help is greatly appreciated. Thanks & Regards, Sup ...

Upgrading to 9.0 on Windows Server 2003 64bit Edition
We are upgrading from 7.0 to 9.0. We have new SQL Servers on which we will perform the upgrade and move into production once complete replacing our existing servers. These SQL Servers are running Windows Server 2003 Enterprise x64 Edition. When attempting to install either GP v7.0 or v7.5 the error message "The image file ...\CDSetup.exe is valid, but is for a machine type other than the current machine." is presented and the installation script will not initialize. Is it possible to install GP 7.0 or 7.5 on the 64bit version of Windows Server 2003 or must we install the 32 bit v...

Round Up and Round Down Time
Good Morning & a Happy New Year to all subscribers in this group. I want to round up Start times and round down Finish times to the next or previous 15 minutes in my spreadsheet. I have found MROUND but this does not work for me in all cases. There does not seem to be MROUNDUP or MROUNDDOWN available. I can get the results I want using a VLOOKUP table but is there another way? Regards to all, Dave Moore =FLOOR(A1,TIME(,15,)) =CEILING(A1,TIME(,15,)) -- David Biddulph DaveMoore wrote: > Good Morning & a Happy New Year to all subscribers in this group. > >...

GP 9.0 Certification
Hello from Las Vegas. I'm interested in becoming a Certified GP 9.0 user to enhance my career. I've looked at the Microsoft Guide to Certification and I'm sure I'm supposed to be going for the MS Certified Business Management Solutions Specialist (I think). I'm registered through CustomerSource and I'm a little confused on what courses I should take since I'm not an IT guy. Any help would be very appreciated. Shawn There is an accounting series of courses that covers foundation, GL, AR, AP, FA etc and another series that covers Inventory, POP, and SOP. You...

Eseutil #9
Hi, Running Eseutil /d offline is not decreasing the size of the store. I ExMerged out about 3Gbs of data and then ran the offline defrag but the size of priv1.edb and priv1.stm remains the same. Please advise. Thanks. On Thu, 29 Dec 2005 18:07:02 -0800, "Gavin" <Gavin@discussions.microsoft.com> wrote: >Hi, > >Running Eseutil /d offline is not decreasing the size of the store. I >ExMerged out about 3Gbs of data and then ran the offline defrag but the size >of priv1.edb and priv1.stm remains the same. Please advise. Thanks. How much free space is avai...

Automatic Rounding problems
I am using Money 99 and, only on investments section, when I try enter a price or quantity with a decimal part, it simply round it to an integer number automatically when I exit the edit box. Do you know how can I make it work correctly. My Contorl Panel / Currency is already configured to 2 decimal digits and it works perfectly in the other fields where I have to put a value. Thanks for your help. Fabio Wasn't Money 99 around before they decimalized stocks? May not be possible to fix it... Fabio Pires wrote: > I am using Money 99 and, only on investments section, > when...

Smartlist Builder since implementing 9.0
Now that we started using 9.0 smartlists made in smartlist builder are not automatically added to the smartlist menu in GP. We do get the message that when opening the smartlist menu that smartlist has detected changes, etc. Should there have been a specific smartlist update as well when we went to 9.0? With 9.0, you no longer have security access to the new smartlists by default. You have to go to Smartlist security and grant access to the new object. While it's a bit of a hassle, the goal is to make sure that not everyone has access to your new Payroll Employee Salary (or whatever)...

Office 4.2.1 and OS 9.2.2
Can I use this version of Office which I had with 8.1. Just upgraded to 9.2.2 and it says "Microsoft dialog library not found". > Can I use this version of Office which I had with 8.1. > Just upgraded to 9.2.2 and it says "Microsoft dialog > library not found". It's not compatible. You'll have to upgrade to (at least) Office 98 or downgrade to Mac OS 8.6. See: http://support.microsoft.com/default.aspx?scid=kb;en- us;288769 -- Bernard Rey MVP office:macintosh ...

Rounding numbers in charts
HHi U'm not very good with computers. And I did a spreadsheet on excel and had percentages I wanted to put in a chart...when I created the chart (piechart) the values came out all inacurate. I wanted one to be 8% and it came out 6%. I heard you couold try and fix it using decimal places but that only makes a minor difference. Is there something I dont know about thats fairly simple to do? Hi Andrea If you'ld like to give us an example of your data (e.g. three categories and the associated percentages) and explain to us where the percentages came from - did you type th...

blocked attachments #9
I can't seem to recieve acrobat attachments. They are getting blocked regardless of file size? Thanks for any tips you could provide ...

Reformat data from 3 columns to 9 columns worksheet
I would like to expand data from the above 3 columns worksheet to th below 9 columns worksheet. There are 338 names total on this database. I had rearranged about 3 names manually. 300 more to go : ) My boss said that there must be a smarter way. Would you agree wit her. Can you help me ? Thanks - John Burton 'View of 3 columns and 9 columns worksheet (http://www.ezpayroll.net/excel_reformat.htm ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com The following code will...

How to round & sum only the displayed (rounded) numbers
I have rounded some numbers to the thousands digits. I need help finding a formula that rounds the displayed (rounded thousands) digits. Everything I try only calculates it based on the unrounded numbers and as such many sum numbers are off by 1 (or really 1,000). Any help would be awesome! thanks On Fri, 19 Feb 2010 06:57:01 -0800, ALG <ALG@discussions.microsoft.com> wrote: >I have rounded some numbers to the thousands digits. I need help finding a >formula that rounds the displayed (rounded thousands) digits. Everything I >try only calculates it based on t...

Pivot Tables #9
Ello I have a list like below Ref Client Name Team TeamMat 1 John 1 Ia 2 Steve 1 Ima 3 Andres 2 Jiv 4 Micel 1 Ima 5 Miquel 2 Jiv I wante to create pivot table to count the unique number of people in team, above team 1 has 2, team 2 has 1 Cant seem to do it with pivot table, is there way or create different type of table useing formulae Thanks for hel Tibow Hi Tibow b...

Time, & Round?
My spread sheet is used to track arrival times at a specific location. The location has a scheduled arrival time in column C, in column D I enter the actual arrival time when a vehicle arrives, and column E calculates the difference. I use the 1904 time system so I’ll be able to calculate and show negative time. Column E is set with =IF(D3=””,””,D3-C3) this will have column E appear blank until the actual time is entered. Without =IF(D3=””,””,D3-C3)in column E and with =D3-C3 this will show the negative of the scheduled time i.e. -4:59 or -18:44. The schedule time is always pr...

Formula for rounding numbers.
I would like the formula for the following situation. How do I make all numbers in cell A3 from 1-32 round up to 33, all numbers 34-65 round up to 66 and all numbers 67-99 round up to 100? Thank you for any help. Alexis Hi Alexis See if =INT(CEILING(A3,100/3)) does what you want. HTH. Best wishes Harald "Alexis Anthony" <alexisanthony1@yahoo.com> skrev i melding news:56c40e33.0405021336.1aa773f0@posting.google.com... > I would like the formula for the following situation. > > How do I make all numbers in cell A3 from 1-32 round up to 33, all > numbers 34-65 r...

charts #9
Hi, I need some help creating a chart with two lines and one column. I have already created a Line - column on 2 axis, but i cannot figure out how to do 2 lines with 1 column on the same chart. An example of data i need charted: Q1 Q2 Q3 Q4 volume(units) 100 200 250 300 price1($) 10.00 11.00 11.25 11.45 price2($) 11.00 10.80 10.65 From this data i already have the volume and price1, but when i try to add price2 the data for price1 doesn't remain a line. Can anyone help? thanks... ...

Rounding to
I have a column of numbers, all multiples of 10, starting at 100 and going up into the thousands. I want to round them to the nearest 50, so 140 would become 150 and so would 160. How do I do that? TIA Steven I plan to live forever.....or die trying! =ROUND(A1/50,0)*50 -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Steven" <none@myisp.com> wrote in message news:#2XwkuAEHHA.1224@TK2MSFTNGP04.phx.gbl... > I have a column of numbers, all multiples of 10, starting at 100 and going > up into the thousands. I want...

Row height #9
Row height autofit isn't working for non-merged cells. Have ensure no spaces following text. What happens when you autofit that row? How much text do you have in that cell? Sharon wrote: > > Row height autofit isn't working for non-merged cells. Have ensure no spaces > following text. -- Dave Peterson ...

Printing #9
I work like to know how to set up the print function so I can print multiple worksheets in the same wookbook by just clicking the print button. Hi Tom Select the Sheets and print Don't forget to ungroup the sheets Or use VBA ActiveWorkbook.PrintOut 'the whole workbook Worksheets.PrintOut 'all worksheets Sheets.PrintOut 'all sheets Sheets(Array("Sheet1", "Sheet3")).PrintOut 'all sheets in the array ActiveWindow.SelectedSheets.PrintOut 'print all selected sheets ActiveSheet.PrintOut 'only the activesheet Sheets("Sheet1").Print...

VLOOKUP help #9
I keep getting a #N/A error when using this formula and I don't know why: =VLOOKUP(A2,'Apr-MayMaterial'!A2:D735,4,0) Am I doing something wrong within the formula? See Debra's site for information http://www.contextures.com/xlFunctions02.html#Range Check out this part http://www.contextures.com/xlFunctions02.html#Trouble -- Regards Ron de Bruin http://www.rondebruin.nl "Lucien" <Lucien@discussions.microsoft.com> wrote in message news:4A03ED15-5092-4B06-81C8-78FEF5BAA7E3@microsoft.com... >I keep getting a #N/A error when using this formula and I don&...

Adding round bitmap
Hi, everybody: Is it possible to add bitmap picture to my dialog which is not a rectangle? If I do it the simple way - add picture control to my dialog (which is rectangle) and then assign to it some bitmap, which is not rectangle, dark background can be seen in the places where bitmap doesn't fill in picture control. Is it possible to avoid it? Thanks, Alex I would use CDC::SelectClipRgn() to set the region of the DC to the shape you want, rounded rect in your case, and then do the bitblt. AliR. "Alex" <alsim123@hotmail.com> wrote in message news:1177602846.2566...

Outlook Reminders #9
Hi all. I'm having a bit of a problem with Outlook 2003, namely after I open it and it tries to display my set reminders it freezes on the Reminders window. It will stay like this for 10 minutes or so before finally coming back. I have tried using the /cleanreminders switch. I did this once before and I thought it worked. However, it is happening again. I tried using the switch a couple of more times with no success. Any ideas on what I can do to fix this problem? P.S. I am using Windows 2000 with SP4. Thanks for the help! What about other Office Products? Maybe you can "pack&qu...