Excel VBA Coding - Slab Calculation 1

[ragu111]

I wanted to calculate slab rates in excel through VBA coding.

The slab is like this:

If date diff is 55 days


First 5 days X $. 0 per day = $ 0.00
Next 10 days X $. 5 per day = $. 50.00
Next 10 days X $.10 per day = $.100.00
Thereafter (25 days) X $.15 per day = $.375.00
-----------------------------
Total: $.525.00

Rightnow I’m using “if” function for every row. I like to have a function (VBA Code) on the backend of the worksheet.

Ragu

 

[SkipVought]

Hi,

backend???????

For VBA, please post in VBA Visual Basic for Applications (Microsoft) forum707.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[byundt]

Try the following user-defined function, which must be installed in a regular module sheet. Use it with a formula like:
=SlabRate(50) returns $525.00

Function SlabRate(Days)
SlabRate = Application.Max(0, Days - 5) * 5 + _
            Application.Max(0, Days - 15) * 5 + _
            Application.Max(0, Days - 25) * 5
End Function

Note that your initial problem statement included two decimal points in each number. I wasn't sure if you meant dollars or cents; the code assumes dollars.

You will also note that the code doesn't test for the end of the period and that it multiplies by a "different" value than you had specified. Actually, the two constructions are equivalent because I multiply by the incremental contribution for days in excess of 5, 15 and 25 rather than the unit cost.

To install a function in a regular module sheet:
1) ALT + F11 to open the VBA Editor
2) Use the Insert...Module menu item to create a blank module sheet
3) Paste the suggested code in this module sheet
4) ALT + F11 to return to the spreadsheet

Optional: to add descriptive text (appears at bottom of Function Wizard):
5) ALT + F8 to open the macro window
6) Type in the name of your function in the "Macro name" field at the top
7) Press the "Options" button
8) Enter some descriptive text telling what the function does in the "Description" field
9) Click the "OK" button

If the above procedure doesn't work, then you need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK.

Brad

 

[ragu111]

you really given a good solution to get ride of my BIG Excel Formula.. thankyou very much..

one more question i want to ask is:

when the free time is more then 5 days (like 8 days) the calculation should be as below

If days = 50 days (with 8 days free)

First 8 days         X $. 0 per day = $   0.00
Next  7 days         X $. 5 per day = $. 35.00
Next 10 days         X $.10 per day = $.100.00
Thereafter (25 days) X $.15 per day = $.375.00
                 -----------------------------
                            Total:    $.510.00

and

If days = 50 days (with 20 days free)

First 20 days        X $. 0 per day = $   0.00
Next  0 days         X $. 5 per day = $.  0.00
Next 5 days          X $.10 per day = $. 50.00
Thereafter (25 days) X $.15 per day = $.375.00
                 -----------------------------
                            Total:    $.425.00

and

If days = 50 days (with 33 days free)

First 33 days        X $. 0 per day = $   0.00
Next  0 days         X $. 5 per day = $.  0.00
Next 0 days          X $.10 per day = $.  0.00
Thereafter (17 days) X $.15 per day = $.255.00
                 -----------------------------
                            Total:    $.255.00

if you give me the solution my life will be made....

Ragu

 

[byundt]

Ragu,
Here is a revised function that covers all the bases:

Function SlabRate(Days As Double, FreeDays As Double)
Dim i As Long
For i = FreeDays + 1 To Days
    Select Case i
    Case Is <= 15
        SlabRate = SlabRate + 5
    Case Is <= 25
        SlabRate = SlabRate + 10
    Case Else
        SlabRate = SlabRate + 15
    End Select
Next
End Function

You'll notice that it uses an extra parameter Freedays. So the worksheet formula might become:
=SlabRate(50,20) returns $425

Brad

 

[ragu111]

it work fine Brad

thankyou very much....