Results 1 to 15 of 43
Thread: variable based on total

20131221, 11:03 #1
 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= 025 = $5
2550=$6
5175=$7
76100=$8
100150=$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

20131221, 11:35 #2
 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

The Following User Says Thank You to sdckapr For This Useful Post:
skipro (20131222)

20131222, 10:32 #3
 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?

20131222, 11:33 #4
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,920
 Thanks
 152
 Thanked 746 Times in 678 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
Last edited by Maudibe; 20131222 at 11:47.

20131222, 18:38 #5
 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.Last edited by skipro; 20131222 at 18:49.

20131222, 19:30 #6
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,899
 Thanks
 420
 Thanked 1,585 Times in 1,434 Posts
Skipro,
Rate Table:
Increase base by these amounts when the total hours in column B equals:
HRs= 025 = $5
2550=$6
5175=$7
76100=$8
100150=$10
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
HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20131222, 20:44 #7
 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.

20131222, 21:20 #8
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,920
 Thanks
 152
 Thanked 746 Times in 678 Posts
You need to correct your rate table as according to this table 25 hours is both +$5 & +$6 etc.
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=025 and 2550, 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.
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
RateMod1.png
Maud

20131222, 22:51 #9
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,920
 Thanks
 152
 Thanked 746 Times in 678 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
MaudLast edited by Maudibe; 20131222 at 22:55.

20131223, 00:48 #10
 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 5175 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.

20131223, 06:36 #11
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,899
 Thanks
 420
 Thanked 1,585 Times in 1,434 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.
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

20131223, 06:53 #12
 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?
=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

20131223, 11:07 #13
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,899
 Thanks
 420
 Thanked 1,585 Times in 1,434 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
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.xlsmMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20131223, 12:05 #14
 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
=ratemod($B$3:B3,C3)
And copy it down the column.
Steve

20131223, 13:08 #15
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,920
 Thanks
 152
 Thanked 746 Times in 678 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
Last edited by Maudibe; 20131223 at 15:04. Reason: added reset macro