Excel formulas with round function

%3CLINGO-SUB%20id%3D%22lingo-sub-2360316%22%20slang%3D%22en-US%22%3EExcel%20formulas%20with%20round%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2360316%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20would%20like%20to%20insert%20round()%20to%20a%20range%20of%20highlighted%20cells%20that%20includes%20existing%20formulas.%26nbsp%3B%20Any%20help%3F%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EA%20reference%20%3A%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20found%20a%20macro%20that%20round%20Here%20is%20a%20macro%20that%20will%26nbsp%3B%3C%2FSPAN%3E%3CI%3Ewrap%3C%2FI%3E%3CSPAN%3E%26nbsp%3Ba%20ROUND()%20function%20around%20all%20of%20your%20formulas%20within%20the%20active%20data%20area%20that%20have%20not%20already%20been%20ROUND()'ed.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CDIV%20class%3D%22%22%3ESub%20mcrRound_5_Formulas()%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20For%20Each%20Cell%20In%20Range(Range(%22A1%22)%2C%20ActiveCell.SpecialCells(xlLastCell))%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20If%20Cell.HasFormula%20Then%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20If%20Left(Cell.Formula%2C%207)%20%26lt%3B%26gt%3B%20%22%3DROUND(%22%20Then%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Cell.Formula%20%3D%20%22%3DROUND(%22%20%26amp%3B%20Mid(Cell.Formula%2C%202%2C%201024)%20%26amp%3B%20%22%2C5)%22%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Cell.NumberFormat%20%3D%20%220.00000%22%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20End%20If%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20End%20If%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20Next%20Cell%3CBR%20%2F%3EEnd%20Sub%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2360316%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2360349%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formulas%20with%20round%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2360349%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F928871%22%20target%3D%22_blank%22%3E%40Chan_Tze_Leong%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20explain%20in%20more%20detail%20how%20and%20where%20you%20want%20ROUND%20to%20be%20inserted%20in%20a%20formula%2C%20preferably%20with%20some%20relevant%20examples.%20Perhaps%20you%20could%20attach%20a%20small%20sample%20workbook%20with%20%22before%22%20and%20%22after%22%20formulas.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2360953%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formulas%20with%20round%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2360953%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BIn%20the%20attached%20file%2C%20rating%20is%20based%20on%20achievement%20from%2090%25%20to%20above%20120%25%20(from%20rating%201%20to%205).%20For%20example%20rating%201%20is%20from%20%26lt%3B90%25%20and%20rating%202%20is%20from%2090%25%20to%2095%25.%20If%20the%20achievement%20is%2095.6%25%2C%20it%20should%20be%20rounded%20to%2096%25.%20If%20the%20achievement%20is%2091.3%25%2C%20it%20should%20be%20rounded%20to%2091%25.%20.%20How%20should%20I%20do%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20addition%2C%20I%20have%20inserted%20the%20FileName%20%3DGET.DOCUMENT(88)%20and%20the%20MTD%20Apr%202021%20and%20YTD%20Apr%202021%20is%20based%20on%20the%20FileName.%20However%2C%20when%20I%20change%20Excel%20name%20to%20KPI%20CY2021%20CSRM_Apr%20to%26nbsp%3BKPI%20CY2021%20CSRM_May%2C%20the%20MTD%20and%20YTD%20name%20still%20refers%20to%20Apr%20instead%20of%20May.%20How%20do%20I%20fix%20this%20problem%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2360956%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formulas%20with%20round%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2360956%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F928871%22%20target%3D%22_blank%22%3E%40Chan_Tze_Leong%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

I would like to insert round() to a range of highlighted cells that includes existing formulas.  Any help? 

 

 

A reference :

I found a macro that round Here is a macro that will wrap a ROUND() function around all of your formulas within the active data area that have not already been ROUND()'ed.

Sub mcrRound_5_Formulas()
    For Each Cell In Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell))
        If Cell.HasFormula Then
            If Left(Cell.Formula, 7) <> "=ROUND(" Then
                Cell.Formula = "=ROUND(" & Mid(Cell.Formula, 2, 1024) & ",5)"
                Cell.NumberFormat = "0.00000"
            End If
        End If
    Next Cell
End Sub
6 Replies

@Chan_Tze_Leong 

Please explain in more detail how and where you want ROUND to be inserted in a formula, preferably with some relevant examples. Perhaps you could attach a small sample workbook with "before" and "after" formulas.

@Hans Vogelaar In the attached file, rating is based on achievement from 90% to above 120% (from rating 1 to 5). For example rating 1 is from <90% and rating 2 is from 90% to 95%. If the achievement is 95.6%, it should be rounded to 96%. If the achievement is 91.3%, it should be rounded to 91%. . How should I do?

 

In addition, I have inserted the FileName =GET.DOCUMENT(88) and the MTD Apr 2021 and YTD Apr 2021 is based on the FileName. However, when I change Excel name to KPI CY2021 CSRM_Apr to KPI CY2021 CSRM_May, the MTD and YTD name still refers to Apr instead of May. How do I fix this problem?

@Chan_Tze_Leong 

See my reply in Link part of file name to Excel cell for your question about the filename.

 

Here is a macro:

Sub AddRound()
    Dim rng As Range
    Dim s As String
    Dim p1 As Long
    Dim p2 As Long
    Dim p3 As Long
    Application.ScreenUpdating = False
    For Each rng In Range("AH11:AH32,AN11:AN32").SpecialCells(xlCellTypeFormulas)
        s = rng.Formula
        p2 = InStr(s, "/")
        If p2 > 0 Then
            If InStr(s, "ROUND") = 0 Then
                p1 = InStrRev(s, ",", p2 - 1)
                If p1 = 0 Then
                    p1 = InStrRev(s, "(", p2 - 1)
                    If p1 = 0 Then
                        p1 = InStrRev(s, "=", p2 - 1)
                    End If
                End If
                If p1 = 0 Then
                    MsgBox "Houston, we have a problem with cell " & rng.Address & "!", vbExclamation
                    Stop
                Else
                    p3 = InStr(p2 + 1, s, ")")
                    If p3 = 0 Then
                        p3 = Len(s) + 1
                    End If
                    s = Left(s, p1) & "ROUND(" & Mid(s, p1 + 1, p3 - p1 - 1) & ",2)" & Mid(s, p3)
                    rng.Formula = s
                End If
            End If
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub
Dear Hans, I will have 35 Excel files that have differing length rows for achievement. Could I define these cells under Name Manager as Ach and replace "AH11:AH32,AN11:AN32" with "Ach"? Would the macro still run with no problems?

@Chan_Tze_Leong 

Probably - just try it.