Excel Busn Math 07: When ROUND function Is Required

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

Hi everyone,

I tried to use the Math.Round function inside my UDF Add-In so that it would return a result rounded of to 2 decimal places.

Also, I have this "test table" wherein I could compare the 1.) Results From Manual (left blue column) from 2.) Results From UDF (right blue column).

I used the IF function to test e.g. =IF(CJ14=CM14,"MATCH", "NO MATCH"). I do not understand why there are "NO MATCH" results even though the columns does match. Why did this happen and what should I do to solve the problem?

1. Here's the image of my worksheet



2. Here's the return statement of my UDF

Code:

'This statement returns result into Worksheet Cell
MyCurrencyFunction = Math.Round(CDec(ComputeThis(varX, varY)), 2)

I hope anyone can help me out soon. TIA

is there an easy way to do this??? I am trying to convert the RATE() function to a normal math formula, so I can calculate it within another program... Here is the example I am trying to convert to normal math B1 is the function.

Microsoft Excel - Book1 ___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout B1 B6 =
A B C D 1 Interest Rate 7.00% 2 3 Total Interest 279,017.80 4 # Years in Loan 30 5 # Payments / Year 12 6 Total Payments 360 7 Beginning Principal 200,000.00 8 Ending Balance 0 Sheet2

I often use excel as a fancy calculator. But often I forget to start the
calculator with the = sign. So if I want to add 44 + 66, I forget to
type in an = sign first, an it gives me an error message. Is there a
setting to turn that off, so I can type in 44 + 66 and it gives me an
answer.

I often use excel as a fancy calculator. But often I forget to start the
calculator with the = sign. So if I want to add 44 + 66, I forget to
type in an = sign first, an it gives me an error message. Is there a
setting to turn that off, so I can type in 44 + 66 and it gives me an
answer.

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

I have a Excel workBook with 2 sheets.


Sheet 1 contains 2 columns. And the data is like the below.

Activity code Status
P1 In Progress
P2 Completed
P3 In Progress
P1 completed
P2 in progress
P3 Yet to start

Activity codes will repeat here.


Sheet 2 contains

Activity code Status
P1
P2
P3

The status should be captured from sheet 1 based on the below logic.


The scenario is like this

If all are yet to start I have to return Yetto start

If all are completed I have to return completed

If any one is In Progress I have to return In Progress.

For Example, For P1 I have In Progress and Completed.In this case I have to return In Progress.

Any Help??

Thanks
Sk5567

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!

Hi,

I'm running the following code to find all instances of a value and return the cell references in an array, but keep getting an object required error.

Please help, Thanks.

VB:

 
Sub FindAllCells(SearchRange As Range, FindWhat As Variant, searchSheet As Worksheet) 
     '************************************************************************************
     ' This search will return the cell addresses for all instances of the FindWhat value
     '************************************************************************************
    Dim foundCell As Range, LastCell As Range, ResultRange As Range, Area As Range 
    Dim MaxRow As Long, MaxCol As Long 
    Dim foundCellAddress As String, FirstFound As String 
    Dim i As Integer 
     
     ' this loop in Areas is to find the last cell
     ' of all the areas. That is, the cell whose row
     ' and column are greater than or equal to any cell
     ' in any Area.
    For Each Area In SearchRange.Areas 
        With Area 
            If .Cells(.Cells.count).Row > MaxRow Then 
                MaxRow = .Cells(.Cells.count).Row 
            End If 
            If .Cells(.Cells.count).Column > MaxCol Then 
                MaxCol = .Cells(.Cells.count).Column 
            End If 
        End With 
    Next Area 
    Set LastCell = SearchRange.Worksheet.Cells(MaxRow, MaxCol) 
     
    i = 0 
     'On Error Resume Next
     'On Error GoTo 0
    Stop 
    With searchSheet 
        Set foundCell = SearchRange.Find(what:=FindWhat, _ 
        after:=LastCell, _ 
        LookIn:=xlFormulas, _ 
        LookAt:=xlWhole, _ 
        SearchOrder:=xlByRows, _ 
        MatchCase:=False) 
         
        If Not foundCell Is Nothing Then 
            MsgBox (foundCell) 
            Set FirstFound = Application.Substitute(foundCell.Address, "$", "") 'strip the $ sign
            Set CellsArray(i) = Application.Substitute(foundCell.Address, "$", "") 'strip the $ sign
            MsgBox (CellsArray(i)) 
            Set foundCell = SearchRange.FindNext(after:=foundCell) 
            Do Until False ' Loop forever. We'll "Exit Do" when necessary.
                i = i + 1 
                If (foundCell Is Nothing) Then 
                    Exit Do 
                     
                End If 
                 
                If (foundCell = FirstFound) Then 
                    Exit Do 
                End If 
                 
                If Not foundCell Is Nothing Then 
                    CellsArray(i) = Application.Substitute(foundCell.Address, "$", "") 'strip the $ sign
                End If 
                 
                Set foundCell = SearchRange.FindNext(after:=foundCell) 
            Loop 
        End If 
        Stop 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines