Round Up/round Down

May 12, 2009

i have a list of minutes in cells a1-a5 say 123 256 147 158 235 divided by 60 giving a total of 15.3 hours. i want the hours to round up if over the. 5 mark or round down if under .5 how would i get the desired result?

ADVERTISEMENT

VBA Round Function Vs Worksheet Round Function

Oct 24, 2006

I don't know if there is a setting I'm missing or I'm going mad but when I use the round function in VBA it doesn't round.

I am using Excel 2000. See the example attached.

In the cell A2 I have a value 0.525, cell B2 has a formula "=round(A2,2)" which = 0.53, but cell C2 is assigned via VBA ie
Sheet1.Cells(2, 3).Value = Round(Sheet1.Cells(2, 1).Value, 2)

and the result is 0.52??

View 9 Replies View Related

How To Round Of Value In VBA

Sep 28, 2012

Using vba how to round off the value e.g.: The number 582.356894 has to be rounded off to 582.4 ....

View 1 Replies View Related

Round To Odd /even In Vba

Nov 6, 2009

A cell value is calculated via a formula in vba. I want to round the result down to the nearest odd number or down to the nearest even number, depending on conditions in an other cell. The result is already an integer.

View 8 Replies View Related

Round Down

Jan 7, 2010

Tried the code below to change figures from countless decimal figures to two, however excel is not having any of it,

Range("d:o").Select
Application.WorksheetFunction.RoundDown (2)

View 9 Replies View Related

How To Round Up A Percentage

May 5, 2014

Why is Excel not rounding up a percentage of 93.5 to 94?

View 6 Replies View Related

Round To Whole Number?

Feb 3, 2014

rounding the numbers. I am working on a quote in which quantity is arrived by dividing the sell price by Total sell price. The condition is the result (quantity) should always be a whole number, I can achieve that by cell formatting but when the calculation is done using handheld calculator the results are different.

I need the result to be same if using excel or handheld device i.e quantity in whole number.

View 5 Replies View Related

Sum Literally :: Round Off

Dec 11, 2008

Hi, I have this issue:

1.67 -> DISPLAY 2 ROUNDED

1.67 + 1.67 = DISPLAY 3 ROUNDED

but I would like to know how to sum 1.67 as 2

WANTED -> SUM 2 AS A NUMBER AND NOT A ROUNDED
2+2 = 4

I've been trying various combinations, but currently no joy

View 10 Replies View Related

Round Up In Units Of 80!

Jan 11, 2009

I need to work out how long the batten has to be so the roof sheets fit evenly, the measurement has to start from 1460mm and go up in increments of 80mm eg 1540mm, 1620mm, 1700mm and so on.

But the number has be closest increment of 80mm over the shed width if this makes sense, the size of the battens for 2400 width shed would be 2420mm but i need this to work out for any width shed not just 2400.

View 3 Replies View Related

Round To The Nearest 5

Feb 20, 2009

I am using the rounding function in excel and its rounding to the 10's, is there a way to round to the nearest 5?

View 4 Replies View Related

Cell To Round Itself..

Aug 5, 2009

i want a cell to round itself. i have a form i'm filling out, based on a percentage of a dollar amount. when the formula calculates, it only shows the first 2 numbers past the dollar point. however, the cell still "knows" what the number is. I have several of these formulas on a spreadsheet, and the sum of them at the bottom of the page is NOT what would you would get adding up the numbers you see on the page, as it is calculating numbers you can't see.

View 5 Replies View Related

Round Up Formula

Sep 24, 2009

I am trying to do is have the roundup formula round up the result of a more complex formula BUT do it all inside of the same cell? The formula I have is in cell A1 and currently I have to have the cell that contains the round up formula (in cell A2) and have it reference A1. The complex formula is =((280283.47/798186.89)*(700*20*4)) and the result is -19,664.41 which I want to round up to $20,000. Is there a way to make this all occur in just cell A2 or am I stretching it?

View 4 Replies View Related

Round The Numbers

Oct 7, 2009

I have a number say:

156.9679628 then I go to format to change it to 2 decimal then it would show

156.97 and then I c&p that ... I still get 156.96796284 how do i just get

156.97

View 2 Replies View Related

Use IF And ROUND In Same Calculation

Oct 23, 2009

I'm creating a spreadsheet to calculate materials with the following columns Cost/10% of Cost/Customer Cost/Qty/Total cost.

I understand that whilst showing rounded to 2 decimal places excel stores more than this in the cell. which then throws out the Total cost by a few pence.

My research leads me to believe I need to use the ROUND function but I'm unsure which cell to use it or how.

View 2 Replies View Related

Round To Two Numbers

Jan 26, 2010

round to two numbers. i have this formula

View 3 Replies View Related

Round Off A Date

Jan 27, 2010

How do I take a date value and round it off to a fixed month and day of the year following the year entered.

Ex: In cell A1 I have a date: 12/28/2009

I want to round off the date to January 1 of the next year. So I need a function that would return 01/01/2010 (jan 1 2010).

View 5 Replies View Related

Round Up All Values

Feb 11, 2010

How do I get Excel to round up all values from .3 onwards. EG 2.36 to 3 and 2.29 to 2?

View 4 Replies View Related

Round Numbers

Feb 22, 2010

19.25581
1.01346
3.29376
0.25337
0.25337
1.26683
Total: 25.33660

If I use the Decrease Decimal on the total 25.33660, it becomes 25.34. How do I round all the sub numbers so that 25.34 is achieved.

View 14 Replies View Related

Getting Averages To Round Up?

Mar 8, 2013

I have a spread sheet with col's that I want to average their totals, but would like them to be rounded up or down.

View 3 Replies View Related

Round Len Function

Aug 14, 2006

I have this formula which works well, but the 3-len part needs to be
dependent on another cells content. Is it possible to have the formula
automatically accommodate for this?

Eg

=ROUND(I36-I37,3-LEN(INT(Q16)))

in cell Q16 is a number 0.010 as displayed, and the sum of i36-i37=
0.007

I need the formula to say ok the minimum division size is in multiples
of Q16 (0.010) and change the 0.007 to either up or down depending on
the calculation.

This works currently, but the figure in Q16 can be 0.1 or 0.00001 etc,
and the sum in i36-i37 could be anything also as it is a calculated
error figure. I need the formula to be able to adapt automatically to
the reference DP in Q16.

Namely the "3-" part in the formula needs to accomodate for the Q16
number format changing.

View 14 Replies View Related

Auto Round Up Or Down To Sum To 100%

Apr 10, 2008

I have sales figures from each region and need to convert those to percentage. and then when i try to add up those percentage shown on screen in excel, sometimes they will add up to 100.1% or 99.9%. Then i will have to manualy adjust the one that excel didn't round up but larger comparable to other regions in the 2 dp.

View 13 Replies View Related

Round Up Date..

Oct 8, 2008

When a new employee gets a uniform, after they write their first order, I have to make sure the cost of the unifrom gets deducted from their check. Payroll ends on Sunday every week. I want to use conditional formatting to highlight the cell when it's eligible for deduction/reimbursement. No matter what day they write the order, it won't be eligile until after Sunday. Because the day of the week will vary, what type of formula can I use to highlight the cell? My sale dates are in column "AC" and the reimbursement column is "AF".

View 4 Replies View Related

Round Down To Two Decimals

Dec 16, 2009

i am looking for some formula to round off a decimal number

e.g. A1=8.288
if I use the formula ROUND(A1,2) the result is 8.29.

But I am looking for a result 8.288. That means it shoukd not round off rather the after decimal we should have only two numbers.

8.288 should be 8.28
27.4627 should be 27.46

View 6 Replies View Related

Round To Nearest .05 Or .09

Feb 6, 2010

Below is my current formula. Right now I have it rounding the results to the nearest .09. I would also like to have it round to the nearest .05 as well.

In other words, to be more exact: I need all numbers that end in 0 or 1 to be rounded down to 9; any numbers that end in 7 or 8 to be rounded up to 9; any numbers that end in 2, 3, or 4 to be rounded up to 5; and 6 to be rounded down to 5. And, of course any numbers ending in 5 or 9 shouldn't change.

View 14 Replies View Related

Round Up To Nearest Whole 50?

May 23, 2012

I am using the MROUND function and it will round up or down to the nearest whole 50. Is there a function to round only UP to the nearest whole 50?

ie: 1704 is being rounded to 1700 I need 1704 to rounded to 1750

View 3 Replies View Related

How To Round Dates Up Or Down

Feb 2, 2007

I need to round all dates that are between the 2nd and 15th down to the 1st and all dates greater than the 15th to the 1st of the next month.
i.e. 1/3/01 -> 1/1/01
1/14/01 -> 1/1/01
1/16/01 -> 2/1/01

I could use either a formula or macro.

I tried using the answers posted, but couldn't get them to work properly. I was able to use those answers, however, and modify them to come up with this:

=IF(AND(DAY(A1)>=1,DAY(A1)

View 9 Replies View Related

Round Down Function

Jun 15, 2007

=IF((OR(A1"",B1"")),IF(ROUNDDOWN(A1,0)=ROUNDDOWN('Sheet2'!$E$1,0),C1,IF(ROUNDDOWN(B1,0)=ROUNDDOWN('Sheet2'!$E$1,0),C1,"")))

All cells in this formula are dates in the format dd-mmm-yyyy h:mm (except C1).

The formula works fine only if I type in a date/time like 09-Jun-2007 0:00 in cells A1 or B1. As soon as I revert to an actual date/time eg 09-Jun-2007 9:53, it returns a #VALUE! error.

If I reference only one cell eg:

=IF(A1"",IF(ROUNDDOWN(A1,0)=ROUNDDOWN('Sheet2'!$E$1,0),C1,""),"")

it works fine with or without an actual time.

View 9 Replies View Related

Round The Number UP To The Next Whole

Nov 12, 2007

I want to use a formula that will round the number UP to the next whole if the the tenths is .3 or higher and round DOWN if the tenths is .2 or lower.

ex. 79.1 --> 79
79.2 --> 79
79.3---> 80
79.4---> 80
79.5---> 80
79.6---> 80
79.7---> 80
79.8---> 80
79.9---> 80

I am thinking this is either not possible or would be an If Then type formula but way beyond my knowledge.

The reason I want to do this is becaue if my student has a 79.5 avg for instance I would normally round to 80. However if they are off by just .1 (ex 79.4) i still want to give them the 80 and then I decided to make it within .2 (ex. 79.3.)

View 9 Replies View Related

Using Round () Function

Apr 4, 2008

I want to use the Round Function where i have a list of dates. I want the Round Function to count the number of dates in the list. I tried to use Round(C4:C16,0), but i didn't work.

View 9 Replies View Related

Round Off Decimal

Apr 25, 2008

I have excelsheet with the following data

A - 1.3
B - 1.3

SUM: 2.6

Now when I want get sum of A and B it shoud be 2

i.e. Round figure of

A - 1
B - 1

SUM : 2

Whereas I'm getting sum as 3 instead of 2 as round figure of 2.6 is 3

View 9 Replies View Related

Round To A Certain Number

Jun 11, 2008

I have budgets that I want to round to certain numbers

For example:
55% rounds to 75%
45% rounds to 50%
33% rounds to 35%

And so on. I have about 7 of them to do, I'm hoping that someone can help with this. I have been struggling all morning on it.

View 9 Replies View Related

Round Formula

Jul 8, 2008

I have value in one cell is 3.75 require formula or format through which it should show as 4.00

View 9 Replies View Related

Round The Dates

Feb 20, 2009

I have a series of dates that I simply want to round down so they all start on the 1st of each month.

The range goes from 01/01/2010 to 03/01/2010, and this continues every month for 4 years.

Is there any way I can simply round each date down?

View 9 Replies View Related

Convert Over All To Round

Aug 20, 2009

I am trying to 1) covert the overall draft position to the round and pick for a 12 team league and 2) express it in a XX (round).XX(pick) format. For example

Example 1
Player X was taken 41st overall. In a draft with 12 teams that works out to the 4th round, 5th pick. This is usually expressed as 4.05. I cannot simply divide 41 by 12 (3.416) and get the intended result in the correct (desired) format of 4.05.

Example 2
Player X was taken 95st overall. In a draft with 14 teams that works out to the 7th round, 11th pick. This is usually expressed as 7.11. I cannot simply divide 95 by 14 (6.78) and get the intended result in the correct (desired) format of 4.05.

View 13 Replies View Related

Round Based On Value

Nov 7, 2009

I am trying to round a dollar amount to two decimal places but the increments aren't consistent. I need to round to .25, .50, .75, or .95. In the attached code I am rounding the number then concatenating to a price book. I have a spreadsheet with several hundred lines and I am hoping to find a cleaner solution ...

View 9 Replies View Related

Round Function

May 16, 2006

I am suspecting that round function is playing a trick on me (N3 = Round(Rnd() * 10, 0) / 10 ). Although sometime it appear to be equal in fact they are not (Reponse = verifie) because of the round function

Sub auto_open()
Dim N1 As Integer
Dim N2 As Integer
Dim N3
Dim Reponse As Double

Dim Compteur As Integer
Dim Score As Integer
verifie = 0
Reponse = 0
N1 = 0
N2 = 0
N3 = 0
On Error Goto ErrorHandler
Score = 0
Randomize
MsgBox "Welcome in the mental calculation program "...............

View 8 Replies View Related

Round To Specified Criteria

Jul 23, 2006

How would I write a formula to ROUND as per the following criteria:

If number is between .00 and .50 ROUNDUP to nearest .25 or .50 (only those two numbers).

If >.50 and less then the next whole number then ROUND to .95

Examples:

$3.29 = $3.50

$3.51 = $3.95

$5.99 = $5.95

$6.03 = $6.25

$4.42 = $4.50

$5.09 = $5.25

The only three possible numbers to the right of the decimal are .25, .50 and .95.

View 7 Replies View Related

Round A Number

Feb 2, 2007

Is there a code that turns 4.123 to 4.5, so it rounds .123 to .5

And same for 4.8 a code turns it to 5, so it rounds .5 to 1 and add it to 4

I tried with round, but……………..

View 4 Replies View Related

Round Buttons

Mar 27, 2007

Is there a way to put round, or any other shape, buttons in vba forms?

View 6 Replies View Related

Inflate & Round

May 1, 2007

How do i inflate and round in vb?

124500 * 1.07 rounded to the nearest 100th?

View 9 Replies View Related

Round Up To Nearest X`

Jun 9, 2007

I trying to make a formula that will multiply a cell by 1.43 and then round the total up to the nearest 45 or 95 with no decimal points.

View 9 Replies View Related

ADVERTISEMENT