Excel Basics #14: ROUND function & Number Formatting

Here how it goes :

Have a VB function :

Public Function MyRound(Data)
MyRound = Round(Data, 1)
End Function

Have a excel sheet with cells on one side with the "MyRound" function and on
the other side the Round function from Excel, guess what they don't give the
same results.

Here the example :
Data Fonction Round Round Excel
=Myround(A1) =Round(A1;1)
0,15 0,2 0,2 Ok
0,25 0,2 0,3 Wrong from VB
0,35 0,4 0,4 Ok
0,45 0,4 0,5 Wrong again From VB

My understanding of the error is : don't have an odd number with an even
one, VB will not round your numbers ????

I've tried this with Excel 2003 and 2000, same results.

I'm asking Microsoft to post a official answer to this and also posting a
bug report for it, and to give a fix.

Thanks and lookout for those even numbers !! Don't you find this Odd ??

--
Bud

Does anyone know how to use the Round Function or the Ceiling function to
round up to a specific number? Example: I have the number 1288 in D8 and I
have a formula in E8 which says “=D8/0.67”, the result is 1922. But I would
like “1922” to round up to “1925”. Had it been “1927”, I would want it to
round up to 1929. Can anyone tell me how to do this?
--
Angie33

Hi all,

Could someone help to convert numbers below to 9 before .cent value by using function "ROUND" or VB code.

I have in column A numbers like 183.35, 195.57, 220.01 etc... expecting the results should be 179.00, 199.00, 219.00 etc... in column B. Please see the sample below:

183.35 179
195.57 199
220.01 219
207.79 209
195.57 199
183.35 179
195.57 199
207.79 209
195.57 199
207.79 209
207.79 219

Thanks heaps.

cheers,
bogia

i want to make a cell red if the formula's value does not equal another cell. What is the formula?

Thanks, Howie

Hi,

I am trying to write a VBA code to go through a range of data and and round any cell that has a number.

I am running into 2 problems and cannot get it to work. Please help
1. The current code does not round a cell that has a dollar sign even though the format of that cell is "accounting". Whereas, other cell with the same format "accounting" but without dollar sign gets round. I want it to round the one with dollar sign as well. How can I fix my code to do that?

2. I don't want to round any cell that has a formula. I can't seem to get that to work?

Your help is much appreciated. Thanks in advance!

Sub Round()
Dim Rng As Range


On Error Resume Next
Application.DisplayAlerts = False
Set Rng = Application.InputBox("Please select a range to be round with your Mouse then click 'OK' ", _
"SPECIFY RANGE", Type:=8)

On Error GoTo 0
Application.DisplayAlerts = True

If Rng Is Nothing Then
Exit Sub
Else

For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count


'If cell.HasFormula Then 'I want to check if it has formula do nothing, if it has number then round??? can't get this to work


If WorksheetFunction.IsNumber(Rng.Cells(i, j).Value) Then Rng.Cells(i, j).Formula = Application.Round(Rng.Cells(i, j).Value, 0)
Next j
Next i

End If
End Sub

I admit I am lost. I want to do apparently simple thing but somehow it never works...I must be highlighting wrong or doing something out of order etc.

I have a pivot table. Imagine it showing Dept date anc charge...like this:

Dept Date Charge
51 1/1/09 20.00
1/2/09 10.00
2/26/09 10.00
3/6/09 20.00

55 1/1/09 20.00
1/2/09 10.00
2/26/09 10.00
3/6/09 20.00


etc etc I HOPE I DID NOT LOOSE THE FORMATTING!

Anyway I want the first line (the one that shows the dept) BOLDED. I thought I could do dhtis with conditional formatting, applying it the sheett all at once by saying if the value in row A > 0 then bold the line..........but I am not doing right...HOW WOULD YOU DO THIS?

Hi-

I'm working on a spreadsheet where I have a list of prices. The prices will vary for example $45.15, $23.98, $78.25 etc. I'd like to round the decimals to either .47 or .78 therefore my new price would become $45.47, $23.78, and $78.25.

I've looked into different rounding functions however I'm not finding what I need. Any suggestions would be greatly appreciated. Thanks

can whole numbers be "rounded" in excel, either with
the "round" function or thru vba?

I have some calculations that I wish to display the
results as follows:

if results = o-24 then round down to 0
if results = 25-49 then round up to 50

On my calculator, if I divide 462,534.05 by 335 I get 1,380.70. Then if I times 1,380.70 by 335 I get 462,534.50. Which is what I need it to be because I need to calculate the difference of a figure not dividing equally.

On excel, if I divide 462,534.05 by 335 I get 1,380.70. Then if I times 1,380.70 by 335 I get the SAME result of 462,534.05.

I have to account for the difference of not dividing equally so there is some format or accounting function that's preset that I don't want but I have no idea how to fix it or format my excel sheet to calculate as my calculator would.

Please can someone help? Thanks!

I have a spreadsheet that shows a date, because of shipping I need to enter a
date and then calculate three months back and enter that date, For example
Cell A1 March, 2004 and I have to enter into cell B1 January 2004, how do I
get Excel to do this automatically.