Page 1 of 3 123 LastLast
Results 1 to 15 of 43
  1. #1
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts

    variable based on total

    See attached example.
    Hourly rate is $20. Number of hours is column B. Hourly rate is modified when "Pr Req" column [C] = "yes". The rate modification varies by the total of column "B" when column C is "yes". In example, Row 3,5,6,8,9,10, Column C =yes. The totals in column E for each of these rows must include the rate increase in the table below. So, rows 3,5,6 would be increased by $5/hr [to $25/hr], row 8 by $6, row 9 by $7, etc.

    Rate Table:
    Increase base by these amounts when the total hours in column B equals:
    HRs= 0-25 = $5
    25-50=$6
    51-75=$7
    76-100=$8
    100-150=$10


    So, if the total of column "B", with corresponding "yes" in column C =54, then the base rate per hour of $20 is increased to $27/hr.

    My solution is somewhat crude. I have a column G that totals the designated rows for increase in column "B" and then use this formula:
    =G4*IF(G4<=25,5,IF(G4=26,6,IF(G4<51,6,IF(G4=51,7,I F(G4<76,7,IF(G4=76,8,IF(G4<101,8,IF(G4=101,10,IF(G 4<126,10)))))))))

    How can I do this without the extra column G [of totals] and can I simplify the formula?
    Thanks
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    In E3:
    =B3*(20+IF(C3="yes",INDEX({5,6,7,8,10},MATCH(SUMIF ($C$3:C3,"yes",$B$3:B3),{0,25,51,76,100})),0))

    Copy down the column. It may be more efficient to create a rate table of the sums and increase and just do a vlookup, instead of having the array in the formula.

    Steve

    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    skipro (2013-12-22)

  4. #3
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Thanks sdckpr.
    How would I do the rate table you suggested?

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 650 Times in 592 Posts
    This can also be done as a User Defined Function (UDF): =RateMod(Hours, PrReq)

    Enter in cell E3 =RateMod(B3,C3) and then copy down. This assumes that the upper limit values of each bracket (ex. 25, which also equals the lower limit values of the next bracket) references the next bracket up. This can be change by reversing the order of the Case statements.

    There are no extra columns and the formula =RateMod(B3,C3) is about as simple as it gets. Paste the code in a standard module.

    HTH,
    Maud

    Example1.png

    Code:
    Public Function RateMod(Hours As Double, PrReq As String) As Currency
    Dim AdditionalRate As Currency
        If UCase(PrReq) = "YES" Then
            Select Case Hours
                Case 0 To 25
                    AdditionalRate = 5
                Case 25 To 50
                    AdditionalRate = 6
                Case 50 To 75
                    AdditionalRate = 7
                Case 75 To 100
                    AdditionalRate = 8
                Case 100 To 150
                    AdditionalRate = 10
            End Select
        Else: AdditionalRate = 0
        End If
    RateMod = (20 + AdditionalRate) * Hours
    End Function
    Attached Files Attached Files
    Last edited by Maudibe; 2013-12-22 at 11:47.

  6. #5
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Maudibe,
    Thanks. Attached is a slight modification of your example. I added another breakdown I forgot. Both your example and mine become incorrect over 25 hours.
    In my example [example_Revised #2] Col G is total hrs to be counted, col H is the correct rate, col I is your/my rate. I also modified your "Case 25 To 50" to "Case 26 To 50" etc, thinking that was the problem. None gave correct returns over 25 hrs.
    Attached Files Attached Files
    Last edited by skipro; 2013-12-22 at 18:49.

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Skipro,

    Rate Table:
    Increase base by these amounts when the total hours in column B equals:
    HRs= 0-25 = $5
    25-50=$6
    51-75=$7
    76-100=$8
    100-150=$10
    You need to correct your rate table as according to this table 25 hours is both +$5 & +$6 etc.
    So what you need would be something like this:
    <26 hrs = 5
    <51 hrs = 6
    etc.
    Or
    <25 hrs = 5
    <50 hrs = 6

    Which do you want?

    If it is the first one this code:
    Code:
    Public Function cModifiedRate(Hours As Double, PrReq As String) As Currency
    
        Dim cAdditionalRate As Currency
        
        If UCase(PrReq) = "YES" Then
            Select Case Hours
                Case Is < 26
                    cAdditionalRate = 5
                 Case Is < 51
                    cAdditionalRate = 6
                Case Is < 76
                    cAdditionalRate = 7
                Case Is < 101
                    cAdditionalRate = 8
               Case Is < 126
                    cAdditionalRate = 9
               Case Is < 151
                    cAdditionalRate = 10
             End Select
        Else
          cAdditionalRate = 0
        End If
        
      cModifiedRate = (20 + cAdditionalRate) * Hours
      
    End Function   'cModifiedRate
    Otherwise reduce each test number by 1.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #7
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Neither of these work. Get same results. They do not add the total numbers associated with "yes" to get the proper rate. They get same result for every "25" entered. They should increase rate.
    See attached examples for both.
    Attached Files Attached Files

  9. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 650 Times in 592 Posts
    You need to correct your rate table as according to this table 25 hours is both +$5 & +$6 etc.
    RG,

    Actually you don't need to change them. Just like your example, 25 will satisfy all case statements but only the first true statement is processes. If case=0-25 and 25-50, only the first case statement in my code will be processed as well.

    This assumes that the upper limit values of each bracket (ex. 25, which also equals the lower limit values of the next bracket) references the next bracket up.
    Skipro,

    As I stated in my above post, the code in the workbook referenced the higher bracket, I now know you want 25, 50, etc., to reference the lower bracket. In your example rev2, you quoted 101 hours as a rate 0f 30 but in the code, you modified the line:

    Case 101 To 125
    AdditionalRate = 9

    Therefore, the rate should be 29 not 30 and the total of $2,929.00

    Code:
    Public Function RateMod(Hours As Double, PrReq As String) As Currency
    Dim AdditionalRate As Currency
        If UCase(PrReq) = "YES" Then
            Select Case Hours
                Case 0 To 25
                    AdditionalRate = 5
                Case 25 To 50
                    AdditionalRate = 6
                Case 50 To 75
                    AdditionalRate = 7
                Case 75 To 100
                    AdditionalRate = 8
                Case 100 To 125
                    AdditionalRate = 9
                Case 125 To 150
                    AdditionalRate = 10
            End Select
        Else: AdditionalRate = 0
        End If
    RateMod = (20 + AdditionalRate) * Hours
    End Function
    Attached is the modified sheet that corrects everything.

    RateMod1.png

    Maud
    Attached Files Attached Files

  10. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Skipro,

    I have run a check on various scenarios and all appears correct. The reason I am leaving 25, 50, 75, etc., in more that one case statement is that if there are 25.5 hours, or example, it will be included (25 to 50)

    RateMod2.png

    Maud
    Last edited by Maudibe; 2013-12-22 at 22:55.

  11. #10
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Maudibe,
    Figured out we are going in different directions. Yes, you are correct, your method is correct for that row. That is where I did not explain well enough. The rate is dependent on the TOTAL of the hours, not just that row.

    If the first row of hours [B3] is 24 hrs, the rate is for those 24 hours is 25. If the second row [B4] is 24 hours, the rate for the 1st hr is 25, for hour #25, the rate for the other 23 hrs is 26, for hours # 26 to 48. If the third row [B5] is 50 hours, the rate for the 1st 2 hours is 26 for hours 49 & 50, the rate for the next 25 hours is 27 for hours 51-75 and the last 23 hours rate is 28 for hours #76 to 98. I hope this is clear.

    I am attaching an example I hope explains this adequately.
    Attached Files Attached Files

  12. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,
    This assumes that the upper limit values of each bracket (ex. 25, which also equals the lower limit values of the next bracket) references the next bracket up.
    That's exactly what I was trying to get the OP to clarify. As the rates stood you need to assume and we all know about that.

    Yes, the Select construct will execute only the first Case matched but here again it is better to have clarity IMHO as to where the limits are.

    And as we both just found out from the previous post clarity of requirements are sometimes hard to find!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How would I do the rate table you suggested?
    For example, in G1:G5, enter the min for each rate. From your example: 0,25,51,76,100 respectively. Then in H1:H5 enter the add't rate: 5,6,7,8,10. Then you can use the formula in C3:
    =B3*(20+IF(C3="yes",VLOOKUP(SUMIF($C$3:C3,"yes",$B $3:B3),$G$1:$H$5,2),0))

    To lookup the rate based on the total in that table. The table could be placed elsewhere or on another sheet if desired.

    Steve

  14. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    SkiPro,

    I think (on occasion) that this is what you are after.
    Code:
    Option Explicit
    
    Public Function cModifiedRate(dHours As Double, dPrevCumHrs As Double, _
                                  PrReq As String) As Currency
    
        Dim cCurrentCalcRate As Currency
        Dim dOldHrsBal       As Double
        Dim dAddOne          As Double
        Dim dNextLimit       As Double
        Dim iMultiple        As Integer
        Dim dHoursToCalc     As Double
        
        If UCase(PrReq) = "YES" Then
        
          iMultiple = dPrevCumHrs / 25  'Note: I want the integer value only!
          dAddOne = IIf(dPrevCumHrs \ 25 = 0, 1, 0)
          cCurrentCalcRate = iMultiple + 5 + dAddOne + 20 'Note: Hard coded value
          dNextLimit = (iMultiple + 1) * 25
          
          Do
          
            If (dHours + dPrevCumHrs) <= dNextLimit Then
              cModifiedRate = cModifiedRate + (dHours * cCurrentCalcRate)
              dHours = 0
          Debug.Print cCurrentCalcRate, dNextLimit, cModifiedRate
            Else
              dHoursToCalc = dNextLimit - dPrevCumHrs
              cModifiedRate = cModifiedRate + dHoursToCalc * cCurrentCalcRate
              dPrevCumHrs = dNextLimit
              dNextLimit = dNextLimit + 25
              cCurrentCalcRate = cCurrentCalcRate + 1
              dHours = dHours - dHoursToCalc
          Debug.Print cCurrentCalcRate, dNextLimit, cModifiedRate
            End If
            
          Loop Until dHours = 0
          
       Else
         cModifiedRate = dHours * 20
       End If
      
    End Function   'cModifiedRate
    Results look like this with my notations.
    SkiPro.JPG
    Note the addition of the Cum PR column and it's formula as shown in the formula bar.
    You need to type this in D3 then fill down.
    I'd also recommend that you change B2 to be a value and then adjust the macro to reference B2 which will make it much easier to change the Base Rate. HTH
    SKIPro Example_Revised #2.xlsm
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Here is a UDF that I came up with, if you don't want a formula
    Code:
    Option Explicit
    Function RateMod(rSubTotal As Range, sPrReq As String) As Double
      Dim dSubtotal As Double
      Dim dHours As Double
      Dim dRate As Double
      Dim vHours As Variant
      Dim vAdd As Variant
      Dim x As Integer
    
      'Change these as desired
      dRate = 20
      vHours = Array(0, 25, 51, 76, 100)
      vAdd = Array(5, 6, 7, 8, 10)
      dSubtotal = Application.WorksheetFunction.Sum(rSubTotal)
      dHours = rSubTotal(rSubTotal.Count)
      
      If UCase(sPrReq) = "YES" Then
        x = Application.WorksheetFunction.Match(dSubtotal, vHours)
        dRate = dRate + vAdd(x - 1)
      Else
        dRate = dRate
      End If
      RateMod = dRate * dHours
    End Function
    Add it to a module and then in E3 you can use something like:
    =ratemod($B$3:B3,C3)

    And copy it down the column.
    Steve

  16. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 650 Times in 592 Posts
    skipro,

    You have a lot of choices here:

    Here is a macro that does not require you to add any formulas on the sheet to do the calculations. I have provided you with the breakdown. You can continue to add as many values to columns 2 and 3

    HTH,
    Maud

    RateMod3.png

    Code:
    Public Sub RateModification()
    '-------------------------------------
    'DECLARE AND SET VARIABLES
    LastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    Hours = 0
    Subtotal = 0
    '-------------------------------------
    'CALCULATE HOURS, BRACKETS, AND TOTALS
    For I = 3 To LastRow
        If UCase(Cells(I, 3)) = "YES" Then 'PR REQ VALUES
            Hours = Hours + Cells(I, 2)
            Select Case Hours
                Case 0 To 25
                    Total = Hours * 25
                Case 25 To 50
                    Total = (625) + ((Hours - 25) * 26)
                Case 50 To 75
                    Total = (1275) + ((Hours - 50) * 27)
                Case 75 To 100
                    Total = (1950) + ((Hours - 75) * 28)
                Case 100 To 125
                    Total = (2650) + ((Hours - 100) * 29)
                Case 125 To 150
                    Total = (3375) + ((Hours - 125) * 30)
            End Select
            Cells(I, 5) = Total - Subtotal
            Subtotal = Total
            Else:  'NON PR REQ VALUES
                Cells(I, 5) = Cells(I, 2) * 20
                Cells(I, 6) = Cells(I, 2)
        End If
    Next I
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2013-12-23 at 15:04. Reason: added reset macro

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •