How to round the numeric value?

Kindly tell me that how can we round the numeric value?
Example: 1.2 = 1.5, 1.7 = 2.0
0
Utf
4/26/2010 9:59:01 AM
excel.newusers 15348 articles. 2 followers. Follow

9 Replies
902 Views

Similar Articles

[PageSpeed] 19

"Salman Saeed" <Salman Saeed@discussions.microsoft.com> wrote in message 
news:F5460D85-6DEE-4EC5-8E83-A1A5E158966A@microsoft.com...
> Kindly tell me that how can we round the numeric value?
> Example: 1.2 = 1.5, 1.7 = 2.0

Use the RND function..... 

0
Gordon
4/26/2010 10:33:38 AM
=CEILING(A1,0.5)


-- 
Regards
Dave Hawley
www.ozgrid.com
"Salman Saeed" <Salman Saeed@discussions.microsoft.com> wrote in message 
news:F5460D85-6DEE-4EC5-8E83-A1A5E158966A@microsoft.com...
> Kindly tell me that how can we round the numeric value?
> Example: 1.2 = 1.5, 1.7 = 2.0 

0
ozgrid
4/26/2010 10:49:01 AM
Mon, 26 Apr 2010 02:59:01 -0700 from =?Utf-8?B?U2FsbWFuIFNhZWVk?= <=?
Utf-8?B?U2FsbWFuIFNhZWVk?=>:
> 
> Kindly tell me that how can we round the numeric value?
> Example: 1.2 = 1.5, 1.7 = 2.0

I don't know what that is, but it's not rounding.  If you're rounding 
to the nearest whole, 1.2 would round to .0 not 1.5.  Even if you are 
rounding to the nearest half, 1.7 would round to 1.5.

Instead of just giving examples, please state your what you're 
actually trying to accomplish.

-- 
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
                                   http://OakRoadSystems.com
Shikata ga nai...
0
Stan
4/26/2010 11:07:43 AM
"Stan Brown" <the_stan_brown@fastmail.fm> wrote in message 
news:MPG.263f3e0ae7a809d898c19a@news.individual.net...
> Mon, 26 Apr 2010 02:59:01 -0700 from =?Utf-8?B?U2FsbWFuIFNhZWVk?= <=?
> Utf-8?B?U2FsbWFuIFNhZWVk?=>:
>>
>> Kindly tell me that how can we round the numeric value?
>> Example: 1.2 = 1.5, 1.7 = 2.0
>
> I don't know what that is, but it's not rounding.  If you're rounding
> to the nearest whole, 1.2 would round to .0 not 1.5.  Even if you are
> rounding to the nearest half, 1.7 would round to 1.5.
>

The OP could certainly use ROUNDUP on the 1.7 value, but the only way of 
making 1.2 show as 1.5 (AFAIK) would be to use an IF statement.. 

0
Gordon
4/26/2010 11:19:03 AM
In addition to what ozgrid.com provided (which will round UP to the next 
nearest .5 boundary), look at
=MROUND(1.2,0.5)
Since MROUND() rounds up,the effect in this case is the same as 
CIELING(1.2,0.5).  Plus, MROUND() requires that the Analysis ToolPak add-in 
be active/installed.

Just another alternative.  You can also look at FLOOR() if you need to round 
DOWN.

"Salman Saeed" wrote:

> Kindly tell me that how can we round the numeric value?
> Example: 1.2 = 1.5, 1.7 = 2.0
0
Utf
4/26/2010 6:45:01 PM
I think you need =ROUND(A2/0.5,0)*0.5
Here are examples of results from CEIL, MROUND and ROUND
number	=CEILING(A2,0.5)	=MROUND(A2,0.5)	=ROUND(A2/0.5,0)*0.5
1.45	1.5	1.5	1.5
1.5	1.5	1.5	1.5
1.7	2	1.5	1.5
1.9	2	2	2
2	2	2	2
2.1	2.5	2	2
2.45	2.5	2.5	2.5
2.5	2.5	2.5	2.5

best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Salman Saeed" <Salman Saeed@discussions.microsoft.com> wrote in message 
news:F5460D85-6DEE-4EC5-8E83-A1A5E158966A@microsoft.com...
> Kindly tell me that how can we round the numeric value?
> Example: 1.2 = 1.5, 1.7 = 2.0 

0
Bernard
4/26/2010 6:52:36 PM
"JLatham" wrote:
> look at =MROUND(1.2,0.5)
> Since MROUND() rounds up

I don't think so.

> the effect in this case is the same as 
> CIELING(1.2,0.5).

I don't think so.

MROUND(1.2,0.5) is 1, whereas CEILING(1.2,0.5) is 1.5.

MROUND rounds; CEILING rounds up.  Since the latter provides the result that 
Saeed requested (based on his two examples), CEILING is the correct function 
to use.

Of course, that begs the questions of whether the two examples sufficiently 
demonstrate the kind of rounding that Saeed really wants, and whether his two 
examples correctly reflect what he wants.  I would like to have seen more 
examples, notably 1.1 and 1.6.


----- original message -----

"JLatham" wrote:
> In addition to what ozgrid.com provided (which will round UP to the next 
> nearest .5 boundary), look at
> =MROUND(1.2,0.5)
> Since MROUND() rounds up,the effect in this case is the same as 
> CIELING(1.2,0.5).  Plus, MROUND() requires that the Analysis ToolPak add-in 
> be active/installed.
> 
> Just another alternative.  You can also look at FLOOR() if you need to round 
> DOWN.
> 
> "Salman Saeed" wrote:
> 
> > Kindly tell me that how can we round the numeric value?
> > Example: 1.2 = 1.5, 1.7 = 2.0
0
Utf
4/26/2010 6:57:01 PM
"Bernard Liengme" <bliengme@TRUENORTH.stfx.ca> wrote:
> I think you need =ROUND(A2/0.5,0)*0.5
[....]
> number =CEILING(A2,0.5) =MROUND(A2,0.5) =ROUND(A2/0.5,0)*0.5
[....]
> 1.7   2   1.5   1.5

But Saeed said that 1.7 should become 2.  So by your own examples, CEILING 
is the only one of those 3 formulas that works for both examples that Saeed 
gave.  (However, Saeed might have misrepresented his requirements.)

Of course, Saleed could use ROUNDUP(A1*2,0)/2, which should be functionally 
equivalent to CEILING(A1,0.5).

I say "should be" because with Excel, one can never be sure.  Sh*t happens! 
;-)


----- original message -----

"Bernard Liengme" <bliengme@TRUENORTH.stfx.ca> wrote in message 
news:eu$WkGX5KHA.1424@TK2MSFTNGP04.phx.gbl...
>I think you need =ROUND(A2/0.5,0)*0.5
> Here are examples of results from CEIL, MROUND and ROUND
> number =CEILING(A2,0.5) =MROUND(A2,0.5) =ROUND(A2/0.5,0)*0.5
> 1.45 1.5 1.5 1.5
> 1.5 1.5 1.5 1.5
> 1.7 2 1.5 1.5
> 1.9 2 2 2
> 2 2 2 2
> 2.1 2.5 2 2
> 2.45 2.5 2.5 2.5
> 2.5 2.5 2.5 2.5
>
> best wishes
> -- 
> Bernard Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
>
> "Salman Saeed" <Salman Saeed@discussions.microsoft.com> wrote in message 
> news:F5460D85-6DEE-4EC5-8E83-A1A5E158966A@microsoft.com...
>> Kindly tell me that how can we round the numeric value?
>> Example: 1.2 = 1.5, 1.7 = 2.0
> 

0
Joe
4/26/2010 7:46:11 PM
Quite right, thanks for the correction.  I'd actually thought MRound() 
rounded, but read something in Help that led me in the other direction - 
quite possibly I was looking at CEILING() help and thinking MROUND().  

As for what the OP really wants?  Who knows, I think everyone is doing the 
typical, reasonable thing -- interpreting based on example data/results 
provided, which is often quite full of holes.

"Joe User" wrote:

> "JLatham" wrote:
> > look at =MROUND(1.2,0.5)
> > Since MROUND() rounds up
> 
> I don't think so.
> 
> > the effect in this case is the same as 
> > CIELING(1.2,0.5).
> 
> I don't think so.
> 
> MROUND(1.2,0.5) is 1, whereas CEILING(1.2,0.5) is 1.5.
> 
> MROUND rounds; CEILING rounds up.  Since the latter provides the result that 
> Saeed requested (based on his two examples), CEILING is the correct function 
> to use.
> 
> Of course, that begs the questions of whether the two examples sufficiently 
> demonstrate the kind of rounding that Saeed really wants, and whether his two 
> examples correctly reflect what he wants.  I would like to have seen more 
> examples, notably 1.1 and 1.6.
> 
> 
> ----- original message -----
> 
> "JLatham" wrote:
> > In addition to what ozgrid.com provided (which will round UP to the next 
> > nearest .5 boundary), look at
> > =MROUND(1.2,0.5)
> > Since MROUND() rounds up,the effect in this case is the same as 
> > CIELING(1.2,0.5).  Plus, MROUND() requires that the Analysis ToolPak add-in 
> > be active/installed.
> > 
> > Just another alternative.  You can also look at FLOOR() if you need to round 
> > DOWN.
> > 
> > "Salman Saeed" wrote:
> > 
> > > Kindly tell me that how can we round the numeric value?
> > > Example: 1.2 = 1.5, 1.7 = 2.0
0
Utf
4/27/2010 1:32:05 PM
Reply:

Similar Artilces:

Numeric content in one cell ( implicit formula ) and the result in another one
Hi, I made some search before, but too much information at the same time. So I post this question: In cell D2 , I have the following content : 10002/(14971213 - 37375) /1000000) In cell E2, I would like to have the result value of data in cell D2 : 669,754 I would not work with "left...len... search... right.." as the format ( then formula ) in cell D2 may change. Is there a function giving the computed result of a cell and put the result in another one? Best regards Pierre In cell E2, will the formula "=D2" do it for you? Or is that too simplistic and I don'...

Macro that deletes values with condition
Hi, I need a macro that deletes duplicates of numbers that appear an odd number of times and that deletes duplicates and the value duplicated an even number of times. Example: Original data A 1 2 3 1 2 1 2 3 4 Result: A 1 2 4 Values 1 and 2 must remain and only delete duplicates because they appear an odd number of times (3), 3 must be deleted because it appears an even number of times (2) and 4 appears because it has no duplicates. Hope this can be done! Thank you so much Hi Insert a heading in row 1 and try this macro: Sub aaa() Dim f As ...

use displayed value in calculation
quick question - if I perform a simple calculation of =(a1+b1) in the m1 cell, and then need to use that result in another calculation in the n1 cell =(m1*3.14), why do I get a value of 0.00 returned? michae What values are in cells A1 and B1? A small number, showing two decimal places, could display as 0.00 michael wrote: > quick question - > > if I perform a simple calculation of =(a1+b1) in the m1 cell, and then > need to use that result in another calculation in the n1 cell > =(m1*3.14), why do I get a value of 0.00 returned? > > > > michae > -- ...

view the reference and not the value
Dear All, I am trying to bring in 5 sheets a value (text) from the first sheet. When i typing the equal (=) in the sheet2 i saw the reference and not the value. Any solution for that ? i am using the XP edition Thanks in advance Manos Hi 1. check that your target cell is not formated as 'Text'. change the format to 'General' and re-enter the formula 2. If this does not help goto 'Tools - Options - View' and uncheck 'Formulas' >-----Original Message----- >Dear All, > > >I am trying to bring in 5 sheets a value (text) from the >firs...

How do you get the maximum value to display in text box form
In a form, if I have 4 text boxes and 3 of them will contain a number. How do you get the maximum value to display in the 4th. I have tried many different things, I can make it work for a single field in a table, but that finds the value through all records. I just want it to display the max value from the numbers on the form, which could be different for various records. You could use something like this as the Control Source of the 4th text box (the underscore is for ease of reading here, and must be removed from the Control Source): =IIf([Field1]>[Field2] And [Field1]>[Field3],...

Charting with zeros or DIV/0 values
Hi - this might have an easy solution, but I'm a bit stuck. I've created a spreadsheet for data entry by another party. This data will be entered on a monthly basis and I only want to update it occasionally. So, I have control charts set up with the basic formulae (percent, mean, upper and lower control limits) in there. I was wondering if there was any way to have all of this data in the range of the chart without having the chart bottom out (i.e. plotting zeros). For example, my columns _might_ look something like this: Date.........Total...Defects...%ofDefects...Mean....UCL....

Finding maximum value
Hello, I want to find the maximum value in a column, but I want to ignore the negative and positive part of the value. Basically, the maximum difference from zero. Example: the maximum value I am looking for will be -0.467. -0.467 0.345 -0.253 0.411 Thanks Ruan One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =INDEX(A1:A4,MATCH(MAX(ABS(A1:A4)),ABS(A1:A4),FALSE)) In article <OGKhnF1KEHA.2100@TK2MSFTNGP10.phx.gbl>, "Ruan" <ruan@aegismed.com> wrote: > Hello, > > I want to find the maximum value in a column, but I want to ignore the > negati...

Using offset in series values of a chart
I have 1 named range created with the offset function on sheet(1): Month=offset($A$1,0,0,counta($A:$A),1) Simple to use as the x-as categories in a chart. For the y-axis I want to use data which can be offset 1, 2, up to 20 columns. Yes I can create 20 named ranges but to do that for 20 sheets in my workbook gets cumbersome. Therefor my question: Can I use the offset function in the series values, like: =offset(Month,0,7) In VBA it it can be done with: ..SeriesCollection(2).Values = Month.Offset(0, 17) But then I have to create one macro for each graph...again cumbersome. Please help. T...

Determine if Value in column A exists in Column B
Hello All- Simple question...I have a sheet with several hundred 10 digit numbers in column A and in Column B. I need a formula to put in column C, that will tell me if the number in A appears anywhere in the list of column B. Doesn't matter if the num,ber in column A appears more than once in column B, just if it appears anywere at all.. A B C 1 02020 02110 True (appears at B2) 2 01121 02020 False 3 01111 98981 False 4 02110 89789 True (appears at B1) 5 19982 58676 ...

Dual Value Axes advice needed please.
Hi all, need some help with what should be a simple chart. Using Excel 2003, I have a worksheet with a rolling year of data, months on row 1, series titles in A2:A4, values in B2:M4 and Annual Total in Column N (data in N2:N4). As the Annual Total is 12 times the average I want the Total to be assigned to a second Value Axis on the right from the default Value Axis on the left. I have no problem setting a complete Series to a second value Axis, but I want to set a category to the second axis. How can I do that? Thanks. -- Spence To add the secondary category axis, go to Chart menu...

Outlook Email To email address dependent on dropdown value
I have the vba code in my word 2003 form to send an email to a specific address etc. What I want to do is depending upon the value of a specific dropdown field send to a different email address. So, if the value is 1 then send to me@thisemail.com but if the value is 2 then sent to them@thisemail.com. I've looked everywhere on this group and can't find an answer. Could someone help me out please. thanks so much you guys are great! The following should work Sub Send_As_Mail_Attachment() ' send the document as an attachment _ in an Outlook Email message Dim b...

Changing Node Values
I have the following XML.. <?xml version="1.0" encoding="UTF-8"?> <EMS> <Scene> <Number_of_Patients>1212</Number_of_Patients> <MCI code_description_type="description"> <Code>Stri</Code> </MCI> <Location_Type code_description_type="description"> <Code>Stri</Code> </Location_Type> <Service_Type code_description_type="description"> <Code>Stri</Code> </Service_Type> </Scene> </EMS> using the following Xpath expression...

cell to have comma-delimited values based on text
I have a table called "220_reference" with a column name "Part Number" having a sample value of below: 4047122(All Dash no.), 4057222(All Dash no.), 4058222(All Dash no.), 4060122(All Dash no.) The entire value is in one cell representing the "Part Number" column(defined as general type so text I suppose). Simple enough. But what I need to do is take any number that has "(All Dash no.)" after it and search through a column in another table to retrieve any rows that have that number(text) in it. The other table name is "220" with ...

Droping the lowest value
Hi I need a formuls to drop the lowest value and add the rest as in..... Cell (AF4) Cell (AH4) Cell (AJ4) Cell (AL4) Cell (AN4) 100 90 80 70 170 What I want the formula to do is drop the lowest number (70) and then add the rest together (100+90+80) and put the total in Cell (AN4) Thanks in advance.. Keith "Kb" <no@spam.com> wrote in message news:445342d4$0$7528$afc38c87@news.optusnet.com.au... > Hi I need a formuls to drop the lowest value and add the rest as in..... > > Cell (AF4) Cell (A...

Outbox Parameter Value Invalid Error
My Gmail is linked to Outlook. Currently, I have a message sitting in the Outbox of email. It won't send after multiple tries, and the error message reads: Task 'Gmail - Sending' reported error (0x80070057) : 'Could not complete the operation. One or more parameter values are not valid.' What does this mean? I am not a very savvy IT person, so layman's terms would be helpful with any offered solutions. Thanks! ...

(Advance Programming) Parameter Returned as Value
Hi, I wish some body could Help Me, I have being loocking for a solution for 2 days. I need to make a simple Query to check if the parameter values are in a Table. I want the Query to return "Founds" and "Not Founds". For Example: Table Definition: TName CREATE TABLE [dbo].[TName] ( [IdName] [bigint] IDENTITY (0, 1) NOT NULL , [Name] [nvarchar] (30) COLLATE Traditional_Spanish_CI_AS NOT NULL ) ON [PRIMARY] Values: Insert Into TName (Name) Values ('Paul') Insert Into TName (Name) Values ('Peter') Insert Into TName (Name) Valu...

criteria default value?
I've created a query that has a from/to criteria as shown below: >=[From Pre-School Number :] And <=[To Pre-School Number :] The possible numeric range is 1-99, so entering 7 and 7 gives you just Pre-School 7's details, or entering 1 and 99 gives you every Pre-School's details. Is there some way to have the "Enter Parameter Value" boxes come up with a default value already in there (say 1 and 99 respectively), which the user can then typeover if they choose. Thanking you in anticipation. You can create an Access Form [frmPreQuery] with 2 Textboxes (defaulted ...

value from field not accepted within onlineform (3.0 -> 4.0)
Hy, We just tried to update our running CRM 3.0 to 4.0 there where nearly no problems! (1 report was not upgradeable) We where very happy. but right now a real problem was discovered: the fields: longitude and latidude from the contact form are no more accepted. Enter a value beetween .... But the values do perfectly match within the range When I delete the values I can save the form. When there is any value in the fields it causes the error: Enter a value ... Best regards for any help ...

Complete unknown values in series
I have a series of lab values vs age. I can plot them on a graph and a line forms between the values forming a curve though not symetrical. Since age is a constant across the graph from 0 to 120 hours, is there a way to get Excel to predict lab values for specific ages based on the curve generated on the graph by the known values? If so, can someone help me through this? Thank you. you can use <chart><add trendline> to have Excel try to fit your data. If you have a theoretical expected fit (Log, Power, exponential, etc) you can tell excel to fit many of those curves. In ...

Question on "Value" function
In trying to use Excel to help solve a mathematical puzzle, I would like to calculate the result of applying the mathematical operator in one cell to the numbers in two other cells. Example: A1 contains 2, B1 contains +, C1 contains 3. In D1, create a formula that will give the result of 2 + 3, i.e. 5. I have tried (among many other attempts) =VALUE(CONCATENATE(A1, B1, C1)), but it gives me a #VALUE! error. What am I missing? Hi this is not possible without using VBA. try the following UDF: Public Function my_calculate(op1 As Range, operand As Range, op2 As Range) my_calculate = Ap...

How do I change X-Axis values in a chart with 2 Y-Axis?
I am attempting to chart two data series that share a X-Axis. The Y-Axis for these series are at different ranges so I have two Y-Axis. When I do this the X-Axis turns into a non-numerical entry, example, 1-2-3-4, when my data is a time and is organized as this (15-30-60-90...etc.) Is there a chart with two Y-Axis and one X-Axis with the plot showing the appropriate spacing that I require? I am using Excel 2002 if that changes anything when You go to <chart><Chart data><series> does it show two different x data sets? if your x axis lables were on the set you chang...

Join based on next closest value (like Excel VLOOKUP)
Trying to do something similar to a VLOOKUP (Excel) in an Access 2003 query. I have the following tables: JOBS Job,Quantity A,96 B,256 C,300 D,4299 COSTS Quantity,Cost 0, $1000 100, $1200 200, $1500 300, $2000 400, $2500 500, $3000 I need a query that takes JOBS.Quantity, looks it up in COSTS.Quantity and find the cost for the NEXT LOWEST quantity. (Example: Job B has a quantity of 256 and the next LOWEST quantity from COSTS is 200 so Job B costs $1500.) The results should be as follows: JOBS.Job,COSTS.Cost A,$1000 B,$1500 C,$2000 D,$3000 This would be ...

Using form to enter "query criteria" (between values)
Good morning everyone, I want to create a form with two boxes, where first box is lower limit of the value and the second upper limit. This values should be used in my query as filter criteria. Like this (query filter criteria): > "textbox1" AND < "textbox2" Does anyone know how I can build this in a form (i.e. connect my textboxes and query criteria)? Kindly, Mikael Sweden By thinking about for another minute I found the (simple) answer. In the query criteria I put the following expression: BETWEEN [forms].[nameofform].[NameofDatefield1] AND [forms].[name...

Replace coloured cells with a value
Hi, I've got 20 or so excel spreadsheets containg some timetable information. It has been entered in a rather odd format that is preventing me analysing it. There are no values, only differently coloured cells! Ideally, I would like to be able to convert the coloured cells into cells with values relating to their colour. I have some VBA experience and am aware of the Font.ColorIndex and Interior.ColorIndex properties that can be evaluated. Does anyone have any ideas on a procedure to convert the cells within a specified range from null values with colours to values with no colours? T...

Changing & recalculating default values on a form
There are a number of occassions in my application where I use a calculated control (1) as the default value for an unbound control (2). Most of the time the user will accept this value and move forward; however, if one of the underlying values upon which the calculated control (1) is based changes, I would like the value of the unbound control (2) to be updated. I do not want to bind the control (2), because there will also be a few times when the user will need to change its (2) value independent of the value calculated in Control (1) Can I do this? Thanks so much. =?Utf-8?B?TGVM...