Assistance Rounding Retail aprices in Excel

[Captain Sisko]

Hi all, first time forum reader. Currently working on some price changes in a retail environment. Trying to figure out the best way to apply the following rounding rules:

[TABLE="width: 966"]
<tbody>[TR]
[TD="colspan: 8"]IF UNDER 10.00 - ROUND DOWN TO CLOSEST .09 (UNLESS .09 OR .19 - THEN ROUND DOWN TO CLOSEST .99)[/TD]
[/TR]
[TR]
[TD="colspan: 8"]IF BETWEEN 10.01 AND 20.00 - ROUND DOWN TO CLOSEST .09 (UNLESS .09, .19, .29, .39, OR .49 - THEN ROUND DOWN TO CLOSEST .99)[/TD]
[/TR]
[TR]
[TD="colspan: 8"]IF 20.01 OR ABOVE - ROUND DOWN TO CLOSEST .99 (UNLESS .79 OR .89 - THEN LEAVE IT ALONE)

With the above rules:

$3.67 would become $3.69
$5.24 would become $5.19
$7.19 would become $6.99
$9.29 would stay $9.29

$13.49 would become $12.99
$17.79 would stay $17.79

$45.69 would become $44.99
$48.89 would stay $48.69

If there is any easy way to apply this automatically with formulas, or macros (or anything!) instead of manually, I would be extremely grateful.

My current Base Price chart can calculate the rounded retail prices and discount prices, but in order to get our actual sale prices to follow the rules above, I am manually entering a modifier. Screen shot link below. Everything in orange is input manually. The first column I just copy over from the original. The Discount percent auto-populates, no problem there. Just the rounding modifier.

The last column is the Base Price. This is just working backward from the modded sale price to the system can calculate the sale price using a Base Price minus discount percentage model.

Thank you in advance for any help!!

Screenshot: https://drive.google.com/file/d/0B4mqtQFlpgt7aDh1bkdXSWNLRlE/view?usp=sharing

Matt[/TD]
[/TR]
</tbody>[/TABLE]

 

[BKrukowski]

In excel you can create you own functions like =Sum(Number1...) you can make one that is =MyRounding(Number1...). Do you have any experience with Excel and VBA to do this? If you don't I am willing to help you but will not be able to until tomorrow.

 

[Captain Sisko]

Unfortunately, I do not have experience with VBA. I've created a few macros with Google's assistance but nothing this advanced. Thank you again.

 

[BKrukowski]

Can you clarify the conditions between 10.01 and 20

Would 10.01 to 10.49 all become 9.99 oror does the value have to end in a 9 to get rounded down to 9.99?
What would you do with 10.38
What would you do with 10.57

Same with Greater than 20

Does 25.84 go to 25.79, 25.89 or 24.99


Also I have considered 5 the upper limit of rounding down (i.e. 10.25 goes to 10.19 not 10.39) is this what you expect?

 

[Captain Sisko]

I've clarified with the owners exactly what rounding rules they are looking for.

For values under $10.00:
X.00 to X.25 would become (X-1).99 (8.04 and 8.25 would both become 7.99, 8.26 would be 8.29)
X.26 to X.99 would round to the closest .X9 value, with the .X5's rounding down. (6.50 and 6.55 would both become 6.49, 8.77 would become 8.79)

For values between $10.01 and $20.00:
XX.00 to XX.45 would become (XX-1).99 (10.01 through 10.45 would all become 9.99, and 10.47 would become 10.59)
XX.46 to XX.99 would round to the closest .X9 value, with the .X5's rounding down. (14.57, 14.60, and 14.65 would all become 14.59, 18.78 would become 18.79)

For values $20.01 and above:
XX.00 to XX.75 would become (XX-1).99 (20.01 through 20.75 would all become 19.99, 34.01 through 34.75 would all become 33.99, etc, and 25.77 would become 25.79)
XX.76 to XX.99 would round to the closest .X9 value, with the .X5's rounding down. (24.77, 24.80, and 24.85 would all become 24.79, 28.88 would become 28.89)

A couple of notes, originally we stated 10.49 would round down, we changed the threshold to .39 in the 10.00 to 20.00 range since the numbers ending in 5's round down, it's about the same)

Also, with numbers ending in 5 rounding down, would this interfere with the other columns in my chart, which follow Excel's rounding rule of 5's rounding up?

Thank you so much!

 

[BKrukowski]

Sisko,

Please try this out to make sure you get what you expect I would copy an old column of work and try it on those because it will be easy to check
So copy the MSRP into Column A in column B1 enter =BJK(A1) and drag down the black box like any other function
then compare that to you rounded column just past it in column C and then in D fo C-B they should all be 0 if it works.
If you have any questions or if there is an issue reply to your post and I will see it.
Good Luck

Public Function BJK(ByVal Number1 As Double) As Double
'To use this in a worksheet just enter =BJK( the number you want to custom round)
'If you want to change the name you can replace BJK from Public Funtion "BJK" above
'If you change BJK above you must also change BJK in the last line of this code'
'This will only work in the workbook you paste it to.
'To place this is a workbook follow these instruuctions
'Step-1 -   Click on the Developer Tab in the Menu Bar if you cannot see it _
            the google how to show the Developer Tab in your version of Excel
'Step 2 -   Click on the "Insert" menu then choose "Module"
'Step 3 -   Copy (Crtl+c) all the code from Public Function BJK(ByVal Number1 as Double) as Double _
            to "End Funtion"
            
'Step 4 -   Paste (Crtl+v) into the Module you just created
'Step 5 -    Save the Workbook
'You can also save the body of this in a txt file and if you accidently delete your workbook _
you will have the Function again
            
Dim X As Integer, Y As Double, Dx As String, Dy As String, IDx As Integer, IDy As Integer
Dim Dpart As String

X = CInt(Number1) 'X = whole number part :When the compuer converts Double to Integer it rounds.
If X > Number1 Then ' Check to see if Number1 was rounded up during conversion
    X = X - 1
End If
Y = Number1 - X 'Y is the cents part
Y = Application.WorksheetFunction.RoundUp(Y, 2) 'When we seperate a double from an _
                                                Interget 1.25 goes to 1.24999999 so _
                                                This corrects the dec error
                                            
Dpart = CStr(Y)     'Y came as three charicters a decimel and two didgets
                    'you can only seperate string charicters not numbers _
                    so this converts a number to a word
Dx = Mid(Y, 3, 1)   ' Seperating the number parts  (0.Dx Dy)
Dy = Right(Y, 1)
IDx = CInt(Dx)      ' Change the string back to a number
IDy = CInt(Dy)      ' String to Number again
If Number1 < 10.01 Then     'The first condition Values less then and equal to 10
    If Y < 0.25 Then           'Condition round down all under 0.25
        X = X - 1
        Y = 0.99
        
        Else
        
        If IDy < 6 Then         'Condition round down 5 and less for numbers _
                                larger than 0.25
            IDx = IDx - 1
            IDy = 9
            
            Dx = CStr(IDx)      'Change the numbersa back to string so they can be
            Dy = CStr(IDy)      'added with worring about place of didget (IDx=4, IDy=3 IDx+IDy = 7 _
                                'As a string IDx + IDy = 43
            Dpart = "0." & Dx & Dy  'This combines the string charc into something _
                                    That can be converted bact to decimel number
            Y = CDbl(Dpart)     'Convert
            Else
            IDy = 9     'satisfies ythe round up 6, 7, 8
            
            Dx = CStr(IDx)
            Dy = CStr(IDy)
            Dpart = "0." & Dx & Dy
            Y = CDbl(Dpart)
            
        End If
        
    End If
    
    Else
    If Number1 < 20.01 Then     'Second Condition
    
        If Y < 0.39 Then        '.39 round down as stated in last reply "Change threshold to .39"
            X = X - 1
            Y = 0.99
            
            Else
            
            If IDy < 6 Then     ' 5 Rounds Down
                IDx = IDx - 1
                IDy = 9
                
                Dx = CStr(IDx)
                Dy = CStr(IDy)
                Dpart = "0." & Dx & Dy
                Y = CDbl(Dpart)
                
                Else
                IDy = 9             '6, 7, 8 rounds up
                Dx = CStr(IDx)
                Dy = CStr(IDy)
                Dpart = "0." & Dx & Dy
                Y = CDbl(Dpart)
                
            End If
        
        End If
    
            Else
            If Number1 >= 20.01 Then     'Last Condition
            
                If Y < 0.75 Then
                X = X - 1
                Y = 0.99
            
                Else
            
                    If IDy < 6 Then
                    IDx = IDx - 1
                    IDy = 9
                    
                    Dx = CStr(IDx)
                    Dy = CStr(IDy)
    
                    Dpart = "0." & Dx & Dy
                    Y = CDbl(Dpart)
                    
                    
                    Else
                    IDy = 9
                    
                    Dx = CStr(IDx)
                    Dy = CStr(IDy)
                    Dpart = "0." & Dx & Dy
                    Y = CDbl(Dpart)
                    End If
        
                End If
            End If
        End If
    End If

MyNum = X + Y
BJK = MyNum ' Print to the Worksheet THIS IS THE OTHER BJK YOU HAVE TO CHANGE_
            'IT MUST MATCH EXACTLY TO WHAT YOU REPLACED AND IS CASE SENSITIVE _

End Function

 

[BKrukowski]

Captain Sisko,

How did it work out? Anything need to be corrected or are you getting unexpected results? I would appreciate your feedback