If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Trying To Put IFs into a Function
Ihave one thing I do in a formula that I don't know or haven't figured out how to make it work in a function. The formula looks like this: IF(AND(299Week1!$H3,Week1!$H3350),4,0))+ (IF(AND(349Week1!$H3,Week1!$H3400),6,0))+ (IF(AND(399Week1!$H3,Week1!$H3450),8,0))+ (IF(AND(449Week1!$H3,Week1!$H3500),10,0))+ (IF(Week1!$H3499,12,0) Simply put, it looks at the value in Worksheet named Week1 - cell H3 (could be H4, H5, H6 depending, also could be Week2 - 17) ) and if that number falls within the listed values, it awards points. Any help putting that into a function that will work with the Week and Offset shown above would be greatly appreciated. In other words I'd like to have a function that looks like this: =Bonus (W, Q) Where the Function Bonus would look something like: Public Function Bonus(W, Q) ? the incorporated IFs pointing to the proper cells End Function Here W is a number that would form the name of the Worksheet to look at ("Week" & W) and Q would be the offset Pointing to cells starting at H2 to pick up the value to analyze. Any help would be most appreciated. Thanks. Steve |
#2
|
|||
|
|||
Trying To Put IFs into a Function
I think this UDF will do what you want...
Function Bonus(WeekNumber As Long, RowOffsetFromH2 As Long) As Double Dim CellValue As Double CellValue = Worksheets("Week" & WeekNumber).Range("H2"). _ Offset(RowOffsetFromH2).Value Select Case CellValue Case Is 300 Bonus = 0 Case Is 350 Bonus = 4 Case Is 400 Bonus = 6 Case Is 450 Bonus = 8 Case Is 500 Bonus = 10 Case Else Bonus = 12 End Select End Function Note that the RowOffsetFromH2 argument is numbered starting at 0 (for H2), then going to 1 (for H3), 2 (for H4), etc. -- Rick (MVP - Excel) "SkippyPB" wrote in message news Ihave one thing I do in a formula that I don't know or haven't figured out how to make it work in a function. The formula looks like this: IF(AND(299Week1!$H3,Week1!$H3350),4,0))+ (IF(AND(349Week1!$H3,Week1!$H3400),6,0))+ (IF(AND(399Week1!$H3,Week1!$H3450),8,0))+ (IF(AND(449Week1!$H3,Week1!$H3500),10,0))+ (IF(Week1!$H3499,12,0) Simply put, it looks at the value in Worksheet named Week1 - cell H3 (could be H4, H5, H6 depending, also could be Week2 - 17) ) and if that number falls within the listed values, it awards points. Any help putting that into a function that will work with the Week and Offset shown above would be greatly appreciated. In other words I'd like to have a function that looks like this: =Bonus (W, Q) Where the Function Bonus would look something like: Public Function Bonus(W, Q) ? the incorporated IFs pointing to the proper cells End Function Here W is a number that would form the name of the Worksheet to look at ("Week" & W) and Q would be the offset Pointing to cells starting at H2 to pick up the value to analyze. Any help would be most appreciated. Thanks. Steve |
#3
|
|||
|
|||
Trying To Put IFs into a Function
Hi
I reduced your formula to this: =IF(AND(299Week1!$H3,Week1!$H3350),4,IF(Week1!$H 3400,6,IF(Week1!$H3450,8,IF(Week1!$H3500,10,12) ))) Public Function Bonus(W As Long, Q As Long) As Long Dim TargetVal As Double Application.Volatile TargetVal = Worksheets("Week" & W).Range("H3").Offset(Q, 0) Select Case TargetVal Case Is 300 Bonus = 0 Case Is 350 Bonus = 4 Case Is 400 Bonus = 6 Case Is 450 Bonus = 8 Case Is 500 Bonus = 10 Case Else Bonus = 12 End Select End Function Regards, Per "SkippyPB" skrev i meddelelsen news Ihave one thing I do in a formula that I don't know or haven't figured out how to make it work in a function. The formula looks like this: IF(AND(299Week1!$H3,Week1!$H3350),4,0))+ (IF(AND(349Week1!$H3,Week1!$H3400),6,0))+ (IF(AND(399Week1!$H3,Week1!$H3450),8,0))+ (IF(AND(449Week1!$H3,Week1!$H3500),10,0))+ (IF(Week1!$H3499,12,0) Simply put, it looks at the value in Worksheet named Week1 - cell H3 (could be H4, H5, H6 depending, also could be Week2 - 17) ) and if that number falls within the listed values, it awards points. Any help putting that into a function that will work with the Week and Offset shown above would be greatly appreciated. In other words I'd like to have a function that looks like this: =Bonus (W, Q) Where the Function Bonus would look something like: Public Function Bonus(W, Q) ? the incorporated IFs pointing to the proper cells End Function Here W is a number that would form the name of the Worksheet to look at ("Week" & W) and Q would be the offset Pointing to cells starting at H2 to pick up the value to analyze. Any help would be most appreciated. Thanks. Steve |
Thread Tools | |
Display Modes | |
|
|