Rounding Formulas

I'm working in a spreasheet that has a lot of formulas, if I want to round 
the number that a formula shows, do I need to do that in a different colmn or 
is there something I can add to the formula to round it to the nearest 
dollar?  Here is an example if the formula I'm often using in this 
spreadsheet.

=((C22*G11)*G17)
0
Chandra1 (5)
7/5/2005 3:10:08 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
368 Views

Similar Articles

[PageSpeed] 54

=round((C22*G11)*G17),0) should do it.   

If you want the result to the nearest PENNY, do this:

=round((C22*G11)*G17*100),0)/100

"Chandra" wrote:

> I'm working in a spreasheet that has a lot of formulas, if I want to round 
> the number that a formula shows, do I need to do that in a different colmn or 
> is there something I can add to the formula to round it to the nearest 
> dollar?  Here is an example if the formula I'm often using in this 
> spreadsheet.
> 
> =((C22*G11)*G17)
0
BarbR (262)
7/5/2005 3:26:03 PM
There is no need to have those extra parenthesis unless there are other 
types of calculations involved (like addition), to round that to the nearest 
dollar would be

=ROUND(C22*G11*G17,0)

-- 
Regards,

Peo Sjoblom

(No private emails please)


"Chandra" <Chandra@discussions.microsoft.com> wrote in message 
news:06A0DDA5-1F5A-4503-931E-5CEE01F11CC6@microsoft.com...
> I'm working in a spreasheet that has a lot of formulas, if I want to round
> the number that a formula shows, do I need to do that in a different colmn 
> or
> is there something I can add to the formula to round it to the nearest
> dollar?  Here is an example if the formula I'm often using in this
> spreadsheet.
>
> =((C22*G11)*G17) 

0
terre081 (3244)
7/5/2005 3:29:43 PM
=ROUND(((C22*G11)*G17),2)

rounds that formula to the nearest penny.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Chandra" <Chandra@discussions.microsoft.com> wrote in message
news:06A0DDA5-1F5A-4503-931E-5CEE01F11CC6@microsoft.com...
> I'm working in a spreasheet that has a lot of formulas, if I want to round
> the number that a formula shows, do I need to do that in a different colmn
or
> is there something I can add to the formula to round it to the nearest
> dollar?  Here is an example if the formula I'm often using in this
> spreadsheet.
>
> =((C22*G11)*G17)


0
bob.phillips1 (6510)
7/5/2005 3:31:12 PM
"=?Utf-8?B?Q2hhbmRyYQ==?=" <Chandra@discussions.microsoft.com> wrote in
news:06A0DDA5-1F5A-4503-931E-5CEE01F11CC6@microsoft.com: 

> I'm working in a spreasheet that has a lot of formulas, if I want to
> round the number that a formula shows, do I need to do that in a
> different colmn or is there something I can add to the formula to
> round it to the nearest dollar?  Here is an example if the formula I'm
> often using in this spreadsheet.
> 
> =((C22*G11)*G17)

Do you just want to round what it SHOWS, or what it calculates? In other 
words, if you're going to be doing more calculations with the value in that 
cell, do you want to do use the rounded off value in the new calculations?

Because if you only need to round off what the cell DISPLAYS, you can do 
that by formatting the cell -- you don't need to change the formula at all.


--
Marc.
0
7/5/2005 4:03:21 PM
Or

    =ROUND(C22*G11*G17,2)

In article <109DEE07-590C-4325-9E5B-3B5FEAFDEC95@microsoft.com>,
 "Barb R." <BarbR@discussions.microsoft.com> wrote:

> If you want the result to the nearest PENNY, do this:
> 
> =round((C22*G11)*G17*100),0)/100
0
jemcgimpsey (6723)
7/5/2005 4:18:05 PM
Reply:

Similar Artilces:

Series Formula
I am trying to create a simple series formula in cell B1. The series has an input from cell A1. For example, if A1 = 4 then B1 should have value = 10 (1+2+3+4). If A1 = 5 then B1 = 15 (1+2+3+4+5). Does anyone know the formula I should put in B1? Thanks Try: =SUMPRODUCT(ROW(INDIRECT("1:"&A1))) HTH Jason Atlanta, GA >-----Original Message----- > >I am trying to create a simple series formula in cell B1. >The series has an input from cell A1. For example, if A1 = >4 then B1 should have value = 10 (1+2+3+4). If A1 = 5 then >B1 = 15 (1+2+3+4+5). Doe...

how to keep formula don't changed when I copy the file the folder
Hi, now I'm meet a problem as follow on Excel 2000 and 2003 I have define one function in one Excel file named "file1.xls" in another excel file(file2.xls) I wanna use the function defined in file1.xls, so I write the formula as following, =file1.xls!function1(A1,A2) in my VBA code, I can sure file2.xls always work with file1.xls together ( that's means when file2.xls reflash, file1.xls always be opened) my problem is: when I copy the file2.xls to another folder and file1.xls still in current folder(for example: D:\test\test1\file1.xls, and file2.xls move to C:\test1\f...

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: >...

How to add to existing formula
I am using the formula below which works good. =COUNTIF(H4:H26,">0.00") I now want to add in cells H31:H55 and H60:H81. When I try to do this I get too few or not enough arguments. I have tried various permutations but cant get it. (lack of knowledge) How would I construct this formula to include the additional cells. thank you "SS" <nonense50@blueyonder.co.uk> wrote in message news:yTU%n.200359$tH4.161127@hurricane... >I am using the formula below which works good. > > =COUNTIF(H4:H26,">0.00") > > I now want to add in cells H3...

formula not working #3
I'm not sure why this isn't working ... please help. A B 0.999 1 0.799 2 0.699 3 0.599 4 0.499 5 0.399 6 0.299 7 0.199 8 0.99 9 I just need to lookup a value in ,say, C1 and compare to colA and return a value in colB. Using: =IF(M41=0,10,IF(M41>1,0,VLOOKUP(M41,$C$60:$D$68,2))) yields #N/A for any value not 0 or >1. What gives? You did not include a 4th argument in your VLOOKUP call. That means that the table has to be sorted ascending. Bit it isn't. Sort your table or use FALSE as 4th argument (which is probably what you require). Make sure the underlying ...

Logical Formula
I need to check two conditions in a cell and then return the value if true or false. I am using the IF function, but not getting thru the second logical condition to be applied. Thanks in advance Mustafa Post your formula, pl. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mustafa S N" <MustafaSN@discussions.microsoft.com> wrote in message news:415212E3-9E12-4990-8527-579009B7AEAB@microsoft.com... >I need to check two conditions in a cell and then return the value if true >or > false. > > I am using the IF function, but not gettin...

Formula to Convert Numbers to Words
What is the formula to convert numbers to words? Example: Number Words 568 Five Hundred Sixty Eight There is no direct functions to convert this. For a VBA solution check out the below links http://support.microsoft.com/kb/213360 http://www.ozgrid.com/VBA/ValueToWords.htm http://www.xldynamic.com/source/xld.xlFAQ0004.html -- Jacob (MVP - Excel) "Kiley" wrote: > What is the formula to convert numbers to words? > > Example: > > Number Words > 568 Five Hundred Si...

letter to number formulas
hi, I'm having trouble finding a way to formulate a cell so when colleague enters a Letter a specifiic cell would display a number valu relating to that letter. For example is the Letter M = 5, then when a colleague enters a M i say A1, a number 5 would automatical be added to A2 I hope I'm clear enough!!! Cheer -- wildoma ----------------------------------------------------------------------- wildomac's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1681 View this thread: http://www.excelforum.com/showthread.php?threadid=32012 Hi as a starting poi...

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*...

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 ...

Cell dsplays the Formula, NOT the result
I have experienced this a few times now, when I enter a formula into a cell (could be something as simple as a SUM, when I press enter or tab, the formula just sists there rather than displaying the result. I have also had this happen to a cell that is displaying the result, but when I edit the formula a;; I see is the formula. This is not something that happens all the time, but when it does it is annoying as I end up typing the formula into a cell a few columns to the right and then dragging it to the cell I need it in. I am using 2003 but have had the same thing happen in 2002 and 9...

Copying formulas to new rows, 20000 times
How can I automatically paste a formula into existing rows. I need to add formulas to 20,000 rows. Help! Put it in the first cell, copy the cell, select the next 19,999 and use Edit / Paste. What is your formula, where is it, and where do you want it, and what do you want it to be when you copy it - Give us an example. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------�------------------------------�---------------- It's easier to beg forgiveness than ask permission :-) ---...

formula in excell
I'm trying to find the formula that will calculate the percentage gain or loss, between two numbers One way: =B1/A1 - 1 (which is a bit more efficent than (B1-A1)/A1, since A1 only has to be looked up once). Format as a percentage. In article <03bd01c378a3$e0e0b110$a401280a@phx.gbl>, "Bill" <spencemi@hotmail.com> wrote: > I'm trying to find the formula that will calculate the > percentage gain or loss, between two numbers ...

Rewriting a formula...
I have a spreadsheet that calculates totals and has formulas in certain columns. Is is possible to have the formulas work, but not have any numbers in the cells before I input them? For example, in column D, I have the simple formula =D9+E9, and so on and so forth down to =D25+E25. I may only put itmes in until D11. So in D12 thru D25, I have $0.00. Can I rewrite this formula, possible an IF/THEN statement to add the two columns, but not show the $0.00 in D12 thru D25, since I did not have to do any addition? My main goal is to not have this info in D12 thru D25 print to make the sheet ...

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. > >...

to many formula
hi i have a worksheet with a lot of data on it column A contains the date column B contains an area column C contains the fault column D contain a quantity A B C D 1/4/04 stores damaged 10 7/4/04 bins rust 60 31/12/04 floor wet 100 what i want to do is return quantity for a date range(one week) once the week range is checked the area and fault are checked i could have 12 faults for one area there...

Printing formulas instead of values
How can I Print formulas instead of values? hit the following keys... CTRL and the (~) sign. (CTRL+~) >-----Original Message----- >How can I Print formulas instead of values? >. > >-----Original Message----- >How can I Print formulas instead of values? >. > Hi you go in to tools, options, view and then tick on the formula box. You then repweat this process to show the vaules. Donna x ...

Sorting by formula results intead of formula text?
I have several row that are linked to other rows =A1 =A2 etc. And so they look like this Smith, Alice Jones, Fred etc. How can I sort the rows containing the formulas so that I see the names in alphabetical order? It appears to sor the formulas themselves, giving, in this case, no change. MS has a knowedgebase article that says how to do the opposite, how to sort the original data without affecting the data shown by the formulas, but that doesn't help me. jeff.seigle@NOSPAMcox.net Do you still need the formulas or can you copy them and paste special as values in place, then ...

Selective formula
Hi all, I would like to create a chart showing spefici numbers regarding a specific count made on a column. To illustrate my need, please see hereunder: Considering I have a table composed of 3 columns: Column A : Device names Column B : Test Scenarii numbers Column C : Test Scripts numbers Devices Test Scenarii # Test Scripts # A 1 1 A 1 2 B 1 3 A 1 4 A 2 2 B ...

Hide a Formula
I am creating a spreadsheet that has formulas I do not want people to see. This spreadsheet has outlines and groups. How can I hide the formulas without protecting the sheets and still show the formula bar. The people I am sending the file to are not Excel literate enough to reset a macro when they open the file, so it needs to open with no problems. -- Jeff Without Protecting the sheet cells - the only idea I can come up with is to use TWO Sheets. In the FIRST put your formulas and calculations and make the SECOND Sheet to be a "reflection" of the FIRST Sheet b...

Date Formula to Count Months
Hi, Can someone help me with a simple date formula? I need to calculate the number of months an employee has been on the job. If they started on the 15th of the month or after, we don't count that month as a month worked. If they leave on the 15th or later, we DO count that as a month worked. My result needs to be a whole number and I want to enter the start date in one cell, the term date in another and have number of months worked appear in another cell. Hi try using DATEDIF. See: http://www.cpearson.com/excel/datedif.htm -- Regards Frank Kabel Frankfurt, Germany Mark wrote: ...

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...

Conditional formula 05-12-10
trying to construct a formula for the following for the same cell: if a2 > 6, then e2 = 0 if a2 = 6, then e2 = 1 if a2 = 5, then e2 = 2 if a2 = 4, then e2 = 3 if a2 = 3, then e2 =5 this is for a golf scoring system. a2 values are hole scores and results are "handicap" point scores. Any help greatly appreciated Try this: =3DIF(A2>6,0,IF(A2<=3D3,5,7-A2)) Hope this helps. Pete On May 12, 4:14=A0pm, desmond1412 <desmond1...@discussions.microsoft.com> wrote: > trying to construct a formula for the following for the same cell: > if a2 > ...

if then formula type
Hello, I'm having problems with a formula to do the following, If column M =>0 then subtract column j from column m and give me the result. Thanx!! ~Julz Hi try =IF(M1>=0,M1-J1,"") -- Regards Frank Kabel Frankfurt, Germany Julz wrote: > Hello, > > I'm having problems with a formula to do the following, > > If column M =>0 then subtract column j from column m and give me the > result. > > > Thanx!! > ~Julz Works like a champ, but it's also giving me M-J when M is blank. When M is blank then my result should be blank. ...