Thread: variable based on total

1. 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

2. 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. Thanks sdckpr.
How would I do the rate table you suggested?

5. 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
Case 25 To 50
Case 50 To 75
Case 75 To 100
Case 100 To 150
End Select
Else: AdditionalRate = 0
End If
RateMod = (20 + AdditionalRate) * Hours
End Function```

6. 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.

7. 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
Case Is < 51
Case Is < 76
Case Is < 101
Case Is < 126
Case Is < 151
End Select
Else
End If

cModifiedRate = (20 + cAdditionalRate) * Hours

End Function   'cModifiedRate```
Otherwise reduce each test number by 1.

HTH

8. 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.

9. 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

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
Case 25 To 50
Case 50 To 75
Case 75 To 100
Case 100 To 125
Case 125 To 150
End Select
Else: AdditionalRate = 0
End If
RateMod = (20 + AdditionalRate) * Hours
End Function```
Attached is the modified sheet that corrects everything.

RateMod1.png

Maud

10. 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

11. 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.

12. 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!

13. 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. 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

15. 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. 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```

Page 1 of 3 123 Last

Posting Permissions

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